Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hello all.
I'm attempting to extract information from a record in a list that is in a list.
The levels can be seen in the attached picture.
My result should be a new list containing just the keyword group and keywords (both [en_us] columns)
Keywords group: Keyword 1
Keywords group: Keyword 2
Keywords group: Keyword 3 and so on
I have been using the following formula in a new column to extract the values:
= try List.Transform([Column2][keywordGroups], each (_[name][en_US]) &": "& _[classifications]{0}[term][en_US]) otherwise null
This formula only gives me the first value, but I have 11 records in the list.
How do I replace the {0} into something dynamic like the _ used in other places in the formula?
Thank you!
Editing to further explain the situation.
I have a very messy API that I'm trying to clean unnecessary data from.
I don't want to use the expend and choose columns option, the api generates about 20 columns that each one is built like this one. In addition, I need this api for multiple reports so I want to create a code that I can copy and paste every time.
I've cleaned most of it but columns like this that have a list inside another list are proving to be challenging.
I wish I could link the data here but it contains sensitive data.
Thank you for helping.
Solved! Go to Solution.
Hi @Sharkybu
I mocked up some data, check if this approach applies. Create a new custom column with below code.
let vGroupName = [KeywordGroups]{0}[name][en_US] in List.Transform([KeywordGroups]{0}[classifications], each vGroupName & ": " & _[term][en_US])
Full code of the sample query:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMlKKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "KeywordGroups", each {[logicalName="logical name", name=[en_US="Keyword group"], classifications={[uri="...uri",term=[en_US="keyword 1"]],[uri="...uri",term=[en_US="keyword 2"]],[uri="...uri",term=[en_US="keyword 3"]],[uri="...uri",term=[en_US="keyword 4"]]}]}),
#"Added Custom2" = Table.AddColumn(#"Added Custom", "extractValues", each let vGroupName = [KeywordGroups]{0}[name][en_US] in List.Transform([KeywordGroups]{0}[classifications], each vGroupName & ": " & _[term][en_US]))
in
#"Added Custom2"
Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!
Hi @Sharkybu
I mocked up some data, check if this approach applies. Create a new custom column with below code.
let vGroupName = [KeywordGroups]{0}[name][en_US] in List.Transform([KeywordGroups]{0}[classifications], each vGroupName & ": " & _[term][en_US])
Full code of the sample query:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMlKKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "KeywordGroups", each {[logicalName="logical name", name=[en_US="Keyword group"], classifications={[uri="...uri",term=[en_US="keyword 1"]],[uri="...uri",term=[en_US="keyword 2"]],[uri="...uri",term=[en_US="keyword 3"]],[uri="...uri",term=[en_US="keyword 4"]]}]}),
#"Added Custom2" = Table.AddColumn(#"Added Custom", "extractValues", each let vGroupName = [KeywordGroups]{0}[name][en_US] in List.Transform([KeywordGroups]{0}[classifications], each vGroupName & ": " & _[term][en_US]))
in
#"Added Custom2"
Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!
Thank you, thank you, thank you.
It worked perfectly.
Without the actual data it is very difficult. I'll have a go.
The are definitly ways in M language to make some shortcuts, but undoable without the actual file...
Thank you very much.
Unfortunately it's more complicated, this is one of many columns in a very messy API.
I'm trying to avoid using the expend commend, and just creating a code that i'll be able to copy every time i'll need to make a report.
How do I replace the {0} into something dynamic like the _ used in other places in the formula?
Iterate a list of indicies; for example:
List.Transform( {0..List.Count(yourList)-1}, (x)=> accessList{x} )
Or use List.Accumulate to do "something" for each list item
Thank you.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
15 | |
12 | |
8 | |
8 | |
7 |
User | Count |
---|---|
15 | |
13 | |
9 | |
7 | |
6 |