Forum Discussion
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.
Thanks in advance,
Gerry
8 Replies
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
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.
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
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?Hi gj-bizguy ,
Please see the parameters explaination of Function.Invoke():
Function.Invoke( function as function, args as list) as anyThe 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/38490Best 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.
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
gj-bizguy sir did you get solution for this drill down all the rows as I am in need of this drill down all columns because I get full text only on drilled down row, and i want all the rows to be drilled down.
Thanks
Sanjay
