Forum Discussion

FlyKick's avatar
FlyKick
Icon for Helper II rankHelper II
5 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.

FlyKick's avatar
FlyKick
Icon for Helper II rankHelper II
4 years ago

Can 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? 

 

  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    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.

    • echristoph's avatar
      echristoph
      Icon for Helper I rankHelper I
      2 years ago

      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.

      • andremoon's avatar
        andremoon
        New Member
        2 years ago

        This is the right solution. I implemented it on a similar case and it works perfectly.

    • FlyKick's avatar
      FlyKick
      Icon for Helper II rankHelper II
      4 years ago

      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's avatar
        v-jingzhang
        Icon for Community Support rankCommunity Support
        4 years ago

        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

    • dkuldip's avatar
      dkuldip
      Regular Visitor
      4 years ago

      Amazing...saved lot of time for me in a similiar issue.