cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JDBOS
Helper III
Helper III

Easy UI question - where is "icon" to Expand a Column with fields from a related Table

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...

JDBOS_0-1620523732316.png

 

 

2 ACCEPTED SOLUTIONS
watkinnc
Solution Sage
Solution Sage

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 

View solution in original post

v-jingzhang
Community Support
Community Support

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.

051201.jpg

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.

View solution in original post

7 REPLIES 7
v-jingzhang
Community Support
Community Support

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.

051201.jpg

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.

View solution in original post

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.

watkinnc
Solution Sage
Solution Sage

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

JDBOS_1-1620588792261.png

 

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.

JDBOS_2-1620589483616.png

 

And the following screenshot shows that Related Inquiry does have related record IDs (so it should be viewed as expandable) but no Expand Icon 

JDBOS_5-1620591081015.png

 

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?

JDBOS_4-1620590670953.png

 

There's so much to learn about PQE and Power BI! 

 

 

watkinnc
Solution Sage
Solution Sage

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 

View solution in original post

@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...

JDBOS_0-1620532149994.png

 

@watkinnc - here's a fresh model - what's the icon above the table column?

 

JDBOS_1-1620534628452.png

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors