Forum Discussion
Helper IIExtract Values From a List of Records
Hi FlyKick
I think I made it!
Add a custom column with below code:
let typeList = [types], typeCount = List.Count([types]) in Text.Combine(List.Transform(List.Numbers(0, typeCount, 1), each Record.Field(typeList{_},"Name")), ", ")Attached the complete M code for your reference.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJycgKSlgZKsTrRSkZAppeXF5A0twQLGAOZjo5AwsQMzDcBMr29vYGkkYVSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, Name = _t, Value = _t]), SourceToList = {Table.ToRecords(Source),Table.ToRecords(Table.RemoveFirstN(Source,1)),{}}, #"Converted to Table" = Table.FromList(SourceToList, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "types"}}), #"Added Custom1" = Table.AddColumn(#"Renamed Columns", "Custom", each let typeList = [types], typeCount = List.Count([types]) in Text.Combine(List.Transform(List.Numbers(0, typeCount, 1), each Record.Field(typeList{_},"Name")), ", ")) in #"Added Custom1"Let me know if you have any questions.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
Community SupportHi FlyKick
I think I made it!
Add a custom column with below code:
let typeList = [types], typeCount = List.Count([types]) in Text.Combine(List.Transform(List.Numbers(0, typeCount, 1), each Record.Field(typeList{_},"Name")), ", ")
Attached the complete M code for your reference.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJycgKSlgZKsTrRSkZAppeXF5A0twQLGAOZjo5AwsQMzDcBMr29vYGkkYVSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, Name = _t, Value = _t]),
SourceToList = {Table.ToRecords(Source),Table.ToRecords(Table.RemoveFirstN(Source,1)),{}},
#"Converted to Table" = Table.FromList(SourceToList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "types"}}),
#"Added Custom1" = Table.AddColumn(#"Renamed Columns", "Custom", each let typeList = [types], typeCount = List.Count([types]) in Text.Combine(List.Transform(List.Numbers(0, typeCount, 1), each Record.Field(typeList{_},"Name")), ", "))
in
#"Added Custom1"
Let me know if you have any questions.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
Helper IIjim you are a true genius! Thank you this has worked perfectly! Much appreciated. Out of interest what is the best practice way to handle this situation? Is extracting the values into a comma seperated list ok or is it recommended to extract them to a seperate table and create a relationship?
- v-jingzhang
FlyKick Thank you! I also struggled two days to work it out 😁
In this situation, I'm not sure what will be the best practise. It's up to you!
At present with Text.Combine, it converts a list of names into a one-line string, which will not add new rows to the table.
Another common practice is to return a list of names in the new column (removing Text.Combine part can achieve this). Then expand the list column to new rows. This will add multiple new rows into the table with other column values copied. This may make it easier when you want to do aggregate calculation per name.
Extracting them to a separate table is also ok. For example, you can remove duplicated values and use the separate table as a Dim Table. Then connect the Dim table to other Fact tables. Create a model like a star schema.
If you are going to have multiple tables in the model, creating a star schema model may be a good choice. See
Understand star schema and the importance for Power BI - Power BI | Microsoft Docs
Best Regards,
Jing- FlyKick
Jing,
WOW I didn't realise how much time you invested to help me out. Truly appreciate it, thats well above and beyond! Thanks for clarifying their is no best practice I thought that might be the case.
Makes sense as their are so many different variables that could impact the "best" solution in this case. Thanks again for all your help. Cheers