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.
Helper IICan anyone confim if it is possible to use the each function to iterate to through the list of records and store the value in an array which we can then use a text.combine to store it as a string in the new custom column?
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.
echristophHelper I
This answer gave me an idea:
= Table.TransformColumns(#"YourPreviousStep", {"types", each Text.Combine(List.Transform(_, each Record.Field(_,"Name")), ", "), type text})This worked for me.
This is the right solution. I implemented it on a similar case and it works perfectly.
- FlyKick
jim 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
Amazing...saved lot of time for me in a similiar issue.
OMG, I've been struggling with this for hours. I'm really grateful for sharing this
Thanks for your help, but I wanted to know how to adapt this code if you want to extract the IDs but with a numeric format. In this case, the columns (Id & Value) are in text format, so the M code works but when I want to extract the IDs, which are in numeric format, there's an error. How can I extract an ID when it's in number format?
The first image is what I want, it works because Id is in text. In my case, my Ids are in number format and when I extract in the same way, I get an error. It works when Id is in text format
slorinSuper User
Bonjour hichem_powerBI
je pense qu'il faut ajouter un Text.From dans la formule pour convertir les nombres en texte avant de les aggréger avec le Text.Combine.
Mettre un mini exemple de vos données éventuellement pour pouvoir tester.
peut-être
= Table.TransformColumns(#"YourPreviousStep", {"types", each Text.Combine(
List.Transform(_, each Text.From(Record.Field(_,"Name"))), ", "), type text})Stéphane
Bonjour Stéphane,
Merci beaucoup pour ta réponse cela fonctionne à merveille.
Hichem.b
