powerquerypowerpivot

Sum of sales of delimiter column

I have a table which contains Item and its Sales.

I need Sum of all items & the column may have delimiter with ",".

How Can I do it in either Power Pivot or Power Query.

Table1

** Expected Output**

Cl_1 Cl_2 Rose 4 Pink,Yellow,Red 17 Louts 3 Green,white 5

Result

Thanking you.

Solution

  • In Power Query, perform the following steps:

    1. Duplicate column Cl_1
    2. Split new column to Rows
      enter image description here
    3. Merge the new split column with Table_1
    4. Expand table to bring in sales
    5. Group by on Cl_1 with sum: enter image description here
    let
  Source = YourTable,
  #"Duplicated column" = Table.DuplicateColumn(Source, "Cl_1", "Cl_1 - Copy"),
  #"Split column by delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Duplicated column", {{"Cl_1 - Copy", Splitter.SplitTextByDelimiter(","), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Cl_1 - Copy"),
  #"Merged queries" = Table.NestedJoin(#"Split column by delimiter", {"Cl_1 - Copy"}, Table_1, {"Items"}, "Table_1", JoinKind.LeftOuter),
  #"Expanded Table_1" = Table.ExpandTableColumn(#"Merged queries", "Table_1", {"sales"}, {"sales"}),
  #"Grouped rows" = Table.Group(#"Expanded Table_1", {"Cl_1"}, {{"Cl_2", each List.Sum([sales]), type nullable number}})
in
  #"Grouped rows"

    Or via DAX you can create a Calculated Column with:

    Cl_2 = 
  CALCULATE(
    SUM(Table_1[sales]),
    FILTER(Table_1, CONTAINSSTRING("," & [Cl_1] & ",", "," & [Items] & ",") )
  )