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.

hichem_powerBI's avatar
hichem_powerBI
Regular Visitor
1 year ago

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

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,

     

    Merci beaucoup pour ta réponse cela fonctionne à merveille.

     

    Hichem.b

  • 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

    :