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

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