Forum Discussion

Kent_10's avatar
Kent_10
Frequent Visitor
2 years ago

How to extract value from List that has Records level keep another Records level?

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 c...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
Anonymous's avatar
Anonymous
Not applicable
2 years ago

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:

  1. Access the List Column: Start by navigating to the Power Query Editor. Locate your "Content. Layout Page Info" column which contains the list.

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

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

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

Kent_10's avatar
Kent_10
Frequent Visitor
2 years ago

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.