Converting List to Group

[MortenK]

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 behind it, and without duplicating the current data which is not structured in lists.  Per the example below, I would like to convert to "Industry (List)" such that I am able to use the data in creating graphs. Help??

Investment	Amount	Industry (List)
Investment 1	USD 1,000,000	E-Commerce
		Travelling

Screenshot:

[MortenK]

Thanks so much Pete. Just a practical question, but given the fact that I have 136 different industry categories, which again can be translated into 14 various sector, would the formula above be the most efficient work around?

I would assume I would need to code for all the various industries AND all the various combinations (fashion, manufacturing).

[BA_Pete]

Hi @MortenK ,

If you want to be able to control by both Sector and Industry then, assuming that Industry is a subset/subcategory of Sector, I would just change these references to point to your [Industry] list column instead:

I'd then create a Sector dimension table that correlates the Sector to Industry groups, and relate this to your fact table on dimension[Sector] ONE : MANY fact[Sector].

Pete

[BA_Pete]

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 · Accepted solution]

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]

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]

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