cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Syndicate_Admin
Administrator
Administrator

How to Perform Power Query (M) Drill down on all Rows

Hello - I have been working on a 3rd party connector that returns a Jobs table in the query editor. In the Jobs table, there is a column called [Accounts] which in the query editor view only contains one value '...'     

I can right click and peform a 'Drill Down' on this and it reveals a text value as a list. How can i perform this across ALL rows so [Accounts] is filled with the string text?

I can see the data is assocaited somehow to the [Accounts] column, but I cannot extract the data on all rows. The importance is that Jobs.[Accounts] = Accounts.[Account Name] for linking the tables after loading.

 

m-query 2.PNGm-query 3.PNG

 

Thanks in advance,

Gerry

7 REPLIES 7
Syndicate_Admin
Administrator
Administrator

Hi @gj-bizguy 

 

That Accounts column only contains ... so there's no other data to extract from it?  I don't see how Drilling Down will give you any more data?

 

When you Drill Down what is revealed?

 

regards

 

Phil

Syndicate_Admin
Administrator
Administrator

Thanks for writing back, Phil---When I drill down, i get the second image in my original post. I am reposting it below . In this example I drill down on the second row with ID = "1004ebe9-"etc, the result is Dixon Projects as a list. So there is some data that is in this column in some form, but I cannot capture all the detail I need.

m-query 3.PNG

 

Syndicate_Admin
Administrator
Administrator

Update - the [ID] Column is of type 'function', and I have never experienced this before. When I use Drill-Down on a specific [Accounts] field, the editor performs the [ID] function and returns [Accounts] as a string. However, I have only figured out how to do this on one ID at a time.

 

Is there anyway to perform the [ID] function to all rows using m-query?

Thanks again

Syndicate_Admin
Administrator
Administrator

I have found that using the Function.Invoke clause, I was able to get a list of errors. Does anybody know how to solve the blow error that is resulting from the Applied Step = Table.AddColumn(table,"newcol", each Function.Invoke(each [ID],[Accounts]))?

 

The error is "Expression.Error: Cannot convert the value "Dixon Projects" to type List." The thing is, i just want the returned value to be "Dixon Projects" in the specific record for [newcol]. Each error recognizes the string text specifically that I want returned in that row.

How can this last step be done?

 

gjbizguy_0-1652473678890.png

 

Syndicate_Admin
Administrator
Administrator

HI @gj-bizguy 

 

Can you please share your file/data.

 

regards

 

Phil

Syndicate_Admin
Administrator
Administrator

Cells can look like this if you have a bunch of white space at the beginiing. Try doing Clean and Trim on the Accounts column to see if this is this issue.

 

https://community.powerbi.com/t5/Power-Query/Ellipsis-in-Power-query-value-field/td-p/2141281

Hi @gj-bizguy ,

Please see the parameters explaination of Function.Invoke():

Function.Invoke( function as function, args as list) as any 

The second parameter requires a list parameter while you have used a column in it so it would remind the convertion error.

 

In addition, you have said that [ID] is a function type, perhaps you can consider sharing a sample query about it and the expected output.

 

Sample data and expected output would help tremendously.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Users online (8,474)