Forum Discussion

FlyKick's avatar
FlyKick
Helper II
4 years ago
Solved

Extract Values From a List of Records

Hi All, After struggling with this for a couple of days I am giving in and seeking help from the Power BI Gods.... Thanks in advance for any tips or pointers, I truly appreciate it and so does my wi...
Need Help
  • v-jingzhang's avatar
    v-jingzhang
    4 years ago

    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.

slorin's avatar
slorin
Super User
1 year ago

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

hichem_powerBI's avatar
hichem_powerBI
Regular Visitor
1 year ago

Bonjour Stéphane,

 

Je voulais savoir s'il y avait une autre possibilité d'obtenir l'information pour chaque enregistrement, donc je ne souhaite pas extraire tous les noms ou toutes les valeurs dans une colonne mais plutôt avoir tous les éléments de la ligne dans une colonne comme sur la photo. 

I wanted to know if there was another way of getting the information for each record, so I don't want to extract all the names or all the values in one column but rather have all the items in the row in a column as in this picture

: