Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
This should be simple but I've spend way too much time learning about Dynamically Expanding Tables when I just need to use the built-in UI in Power Query Editor...
I've inherited a model that has expanded Columns (with fields from a related, external table)
Since some fields were deleted in the source dbase, I need to rebuild these Applied Steps but can't find the Icon to launch the following pop-up screen to Expand a Column. Please let me know where I should be looking...
Solved! Go to Solution.
Click on the Removed Other Columns step, then you can click the icon at the top right of your table column to see the expansion options.
--Nate
Hi @JDBOS
When you connect to a Salesforce Object, you will see an option Include relationship columns in the connection window. This selection alters the query by including columns that might have foreign-key relationships to other tables. If this box is unchecked, you won’t see those columns. This is probably the cause that you don't see the expandable table column in your fresh model.
See document: Power Query Salesforce Objects connector | Microsoft Docs
Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.
Hi @JDBOS
When you connect to a Salesforce Object, you will see an option Include relationship columns in the connection window. This selection alters the query by including columns that might have foreign-key relationships to other tables. If this box is unchecked, you won’t see those columns. This is probably the cause that you don't see the expandable table column in your fresh model.
See document: Power Query Salesforce Objects connector | Microsoft Docs
Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.
Makes good sense that to have Relationships avialable to Power Query Editor, we need to load them by checking the checkbox! I am finding that I need to use "New Source" to see the relationships checkbox.
Thanks for the coaching - and now I see the Expand icon that allow me to Expand.
Ohhh, I see what you mean. You got to go one step further back. Power Query is looking for columns that don't exist, as you correctly point out. If you go back to the Navigation step, from there you can use the Choose Columns from the GUI to re-choose the columns from among those that exist. This will give you a new table column that you can expand from the upper right corner of that column.
--Nate
@watkinnc Here's what I've learned...but I'm still struggling to understand how to get the "Expand Column" icon to appear for columns that should be Tables >> Relative (lookups)
Going back ton the Navigation Applied Step for the Case Record query, I've found the Expand Column icon in the upper right of Related_Inquiry__r field (that's "relative" __r .)
In the lower left of the following screenshot, looking at "Go to Column", I'm seeing both Related_Inquiry__c and Related_Inquiry__r fields.
Related_Inquiry__c is a record id - Related_Inquiry__r is a "value" (displayed in the screenshot directly below) And, looking at the last Applied Step, it's for Expanding Related_Inquiry__r
Creating a fresh model, with just Case Record object/table, at this same Navigation Applied Step, looking at Go to Column (lower left green box) I'm only seeing Related_Inquiry__c and no Expand icon (in purple) >> to show that there are Record IDs in the Related_Inquiry__c field, I've added another screenshot showing that data.
And the following screenshot shows that Related Inquiry does have related record IDs (so it should be viewed as expandable) but no Expand Icon
So how do I get/create the Related_Inquiry__r column?
And a last screenshot showing the Advanced Editor for the Case Record showing the code behind the Applied Steps. Again, how did the Related_Inquirey__r column get created?
There's so much to learn about PQE and Power BI!
Click on the Removed Other Columns step, then you can click the icon at the top right of your table column to see the expansion options.
--Nate
@watkinnc Thx Nate! but clicking on "Removed Other Columns" - shows that I need to clear up the errors which means deleting this Applied Step - so how can I get back to the expansion options? I'll try this with another model and see if that shows me the expansion options...
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.