Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi all,
I have question here to ask how to extract or retrieve values from one List column that has one Records level keep another Records level?
From my Query setting page, I have one column called "Content. Layout Page Info" that is showing List. Inside this List, inside has multiple Records (either two to three). Each of this Records, will keep one Record that consists of 3 info (Content, Value Type and Confidence). I wish to retrieve the "Content" and "Confidence" from the most inner part's Record.
Picture 1
Picture 3
Picture 4
I tried to click the Expand icon button from the "Content. Layout Page Info" column ---> select the "Expand to New rows" ---> then will add more data rows in my query table listing. Initially has only 127 rows, then after press this button then roughly 300 rows updated. From the listing, will see each of the Record will become as new row added. This result is not my expectation, I wish the Records are displaying as new column on each row items.
Please assist on this question and provide me more detailed suggestion as I'm still new in this Power BI. Thank you.
Hi @Kent_10 ,
To achieve your goal, you'll need to use a combination of Power Query transformations to extract the "Content" and "Confidence" from the most inner part of your records. Here's a concise action plan to guide you through:
Access the List Column: Start by navigating to the Power Query Editor. Locate your "Content. Layout Page Info" column which contains the list.
Expand the First Level: Instead of expanding to new rows, you'll want to extract the nested records. Click on the expand button (two arrows) next to the column name, but ensure you select the option to "Extract Values..." or "Expand to New Columns" if available. If "Extract Values..." is the only option, you might need to first convert the list to a table.
Extract Nested Records: Once you have the records at a top level, you might see them as links (indicating records). Click on the expand button next to these to select the fields you're interested in - in your case, "Content" and "Confidence". Make sure to uncheck "Use original column name as prefix" to keep the column names clean.
Refine the Data: After expanding, you may have multiple columns for "Content" and "Confidence". Use the Power Query Editor to rename, reorder, or remove any unnecessary columns to match your desired output structure.
Best Regards,
Xianda Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous
Thanks for your reply here.
At your suggested step 2, I need to extract values then I have to select "Extract Values ..." option. So, I converted the List column as Table at new column called "Custom" by using this formula:
Table.AddColumn(#"Expanded Content", "Custom", each Table.TransformColumns(#"Expanded Content", {"Content.Layout Page Info", each Text.Combine(List.Transform(_, Text.From), ","), type text}))
But I checked my query editor page, I can see this "Custom" column consists of Table as value but when I clicked the two-arrows Expand icon (next to "Custom" column header name) and select the "Content.Layout Info Page" option, it will show me as "Error". Here is the error message:
error_msg_expanded_table
May I know is my formula above to expand List column to Table new column got any error? Can you please advice me this issue? Thank you.
User | Count |
---|---|
141 | |
70 | |
70 | |
53 | |
53 |
User | Count |
---|---|
208 | |
95 | |
64 | |
60 | |
57 |