Forum Discussion

MortenK's avatar
MortenK
Regular Visitor
4 years ago
Solved

Converting List to Group

Hi,   I have made a query towards Airtable, and my data came sorted in Lists. I have not been able to convert this data to Groups such that I am able to organise charts on the basis of the data beh...
Need Help
  • MortenK's avatar
    MortenK
    4 years ago

    Thanks Pete,

     

    I ended doing a walk around and using Table.AddColumns, which leaves me with the first datapoint and access to using the data in the graphics. The first element is the most cruical either way so this was fine. Thank you for the support. 

     

    = Table.AddColumn(#"Renamed Columns1", "Industrials.1", each if Value.Is([#"Industrials Rollup (from Industrials)"],type list) then [#"Industrials Rollup (from Industrials)"]{0}

    else "")

     

    Morten

BA_Pete's avatar
BA_Pete
Super User
4 years ago

Hi MortenK ,

 

In the example provided, you could just expand the list column using the two outward arrows button at the top-right of the column. However, this would duplicate your row, one row for Fashion and another for Manufacturing. 

How would you want the row values apportioned between the different industries?

Would you want the investment value to be split evenly over each industry, or would you want the row to remain distinct, but have the industry group be displayed as "Fashion, Manufacturing"?

 

Pete

  • MortenK's avatar
    MortenK
    Regular Visitor
    4 years ago

    Thanks Pete,

     

    I ended doing a walk around and using Table.AddColumns, which leaves me with the first datapoint and access to using the data in the graphics. The first element is the most cruical either way so this was fine. Thank you for the support. 

     

    = Table.AddColumn(#"Renamed Columns1", "Industrials.1", each if Value.Is([#"Industrials Rollup (from Industrials)"],type list) then [#"Industrials Rollup (from Industrials)"]{0}

    else "")

     

    Morten

  • MortenK's avatar
    MortenK
    Regular Visitor
    4 years ago

    Hi Pete, Greatly appreciate the answer. You are correct in the fact that the first option would duplicate the row, which I would not want as it would also "double" the investment amount etc. I would like that the investment value was split out evenly per each industry. Preferably, If I have two investments (A= 100 and B= 200), and A= Fashion and B= Fashion, Manufacturing, I would like the investment amount to be 200 per fashion and 100 per manufacturing in grand total.

    • BA_Pete's avatar
      BA_Pete
      Super User
      4 years ago

       

      Ok, no problem. Paste this into a new blank query using Advancd Editor to follow the steps I took. It's only the last two steps that you'll actually need to do, the steps before those were just building the table:

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0MFCK1YlWcgKyjaBsZyBbKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [row = _t, #"Calculated original investment" = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"Calculated original investment", Int64.Type}}),
    addSectorListCol = Table.AddColumn(chgTypes, "Sector (from Industrials)", each if [row] = "A" then {"Fashion"}
else if [row] = "B" then {"Fashion", "Manufacturing"}
else null),
    yourStepsStartFromHere = "StartYourStepsFromBelow",
    addSplitCalcOrigInvest =
    Table.AddColumn(
        addSectorListCol,
        "splitCalcOrigInvest",
        each try
            [Calculated original investment] / List.NonNullCount([#"Sector (from Industrials)"])
        otherwise null
    ),
    expandSectorToNewRows = Table.ExpandListColumn(addSplitCalcOrigInvest, "Sector (from Industrials)")
in
    expandSectorToNewRows

       

      This gives me the following output:

       

       Pete