Some ideas for shaping my data.

[trevb]

I've hit my head at this for a while but can't seem to figure it out.  I'm sure there is a really easy way to do this but just can't seem to switch my head to the logic.  I wondered if someone out there can point me in the right direction

I have data that looks like this

ID        Type          Name            2011       2012      2013      2014       2015 

1          A               Test 1            1000       1000      1000      1000       1000

1          B               Test 1B           100        100       1000       100         100

1          B               Test 2B           200        200       200         200         200

1          B               Test 3B           300        300       300         300         300

What I want to do is take away the sum of the type Bs from the type a and return a data set that looks like this

ID        Type          Name            2011       2012      2013      2014       2015 

1          A               Test 1             400        400       400         400         400

1          B               Test 1B           100        100       1000       100         100

1          B               Test 2B           200        200       200         200         200

1          B               Test 3B           300        300       300         300         300

or even just would work for me

ID        Type          Name            2011       2012      2013      2014       2015 

1          A               Test 1             400        400       400         400         400

I've thought of lots of things like itterating through the bs and chaning each column into a - version then just summing.  My problem then was itterating through the columns doing that.  I could not seem to get replace to do this.

I've asked a few question like this before and someone always answers with such a simple solution I feel thick as two short planks!  I'm really hoping someone can do that for me again  🙂

[Greg_Deckler]

@trevb - Are you looking for a DAX solution or Query "M" solution?

[Greg_Deckler]

In DAX, assuming that you only have two test types, otherwise this gets harder, you could do this:

1. Create a "Types" table with just A and B in it in a column called Type

2. Relate your two tabes on Type columns

3. Use a new column formula in your new "Types" table like:

2011 Total = SUMX(RELATEDTABLE(Tests),Tests[2011]) - (SUMX(ALL(Tests),Tests[2011]) - SUMX(RELATEDTABLE(Tests),Tests[2011]))

The math works out to:

1000 - (1600 - 1000) = 1000 - 6000 = 400

It does not work for B, but you could wrap it in an IF statement and do a different calculation for B (leave it alone).

[ImkeF]

This is an M-solution (query-editor) that adjusts dynamically to changing years.

It assumes that the ID-column is your separator for different groups: So all Type-B's with the same ID will be deducted from the same ID's type A. There must only be one TypeA per ID. The entries in column "name" don't matter.

let
    Source = YourTableName,
    UnpivotOther = Table.UnpivotOtherColumns(Source, {"ID", " Type", "Name"}, "Attribute", "Value"),
    Step1 = Table.TransformColumnTypes(UnpivotOther,{{"Value", type number}}),
    #"Filtered Rows" = Table.SelectRows(Step1, each ([#" Type"] = "B")),
    Minus = Table.TransformColumns(#"Filtered Rows", {{"Value", each List.Product({_, -1}), type number}}),
    #"Replaced Value" = Table.ReplaceValue(Minus,"B","A",Replacer.ReplaceText,{" Type"}),
    Group = Table.Group(#"Replaced Value", {"ID", "Attribute", " Type"}, {{"Value", each List.Sum([Value]), type number}}),
    #"Merged Queries" = Table.NestedJoin(Step1,{"ID", "Attribute", " Type"},Group,{"ID", "Attribute", " Type"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Value"}, {"Value.1"}),
    #"Inserted Sum" = Table.AddColumn(#"Expanded NewColumn", "Sum", each List.Sum({[Value], [Value.1]}), type number),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Sum",{"Value", "Value.1"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Attribute]), "Attribute", "Sum", List.Sum)
in
    #"Pivoted Column"

You need to reference to your individual source in the first step.

This is a "clickable solution": Done only by using the UI and no use of edited code in the advanced editor.

So there might be a shorter code, but this solution also allows you to follow the steps once you got it running by clicking on each step and watch how the results evolve. 

[trevb]

Hi Smopre,  I was looking for an M solution as then I can use this both in PowerBI and excel if I have to provide an actual printed report (something it would be useful to have out of BI somehow)

Inke,  Many thanks for this I will give it a try 🙂 I can already see I can learn a lot from this 🙂

You two are amazing  🙂

[ImkeF · Accepted solution]

Hi @trevb. Just realized that my code works on a table where the column "Type" has a leading space " Type". Which is a bit suboptimal.

Should have eaten my own dogfood and shipped the code together with the data as described here:

let
 Source = Table.PromoteHeaders(Table.FromColumns({ {"ID" ,"1" ,"1" ,"1" ,"1" ,"2" ,"2" ,"2"}, {"Type" ,"A" ,"B" ,"B" ,"B" ,"B" ,"A" ,"C"}, {"Name" ,"Test1" ,"Test1B" ,"Test2B" ,"Test3B" ,"T" ,"Z" ,"TestC"}, {"2011" ,"1000" ,"100" ,"200" ,"300" ,"200" ,"100" ,"1"}, {"2012" ,"1000" ,"100" ,"200" ,"300" ,"200" ,"500" ,"1000"}, {"2013" ,"1000" ,"1000" ,"200" ,"300" ,"200" ,"600" ,"200"}, {"2014" ,"1000" ,"100" ,"200" ,"300" ,"200" ,"500" ,"100"}, {"2015 " ,"1000" ,"100" ,"200" ,"300" ,"200" ,"600" ,"50"} })),
 UnpivotOther = Table.UnpivotOtherColumns(Source, {"ID", "Type", "Name"}, "Attribute", "Value"),
 Step1 = Table.TransformColumnTypes(UnpivotOther,{{"Value", type number}}),
 #"Filtered Rows" = Table.SelectRows(Step1, each ([Type] <> "A")),
 Minus = Table.TransformColumns(#"Filtered Rows", {{"Value", each List.Product({_, -1}), type number}}),
 ReplaceType = Table.TransformColumns(Minus,{{"Type", each "A"}}),
 Group = Table.Group(ReplaceType, {"ID", "Attribute", "Type"}, {{"Value", each List.Sum([Value]), type number}}),
 #"Merged Queries" = Table.NestedJoin(Step1,{"ID", "Attribute", "Type"},Group,{"ID", "Attribute", "Type"},"NewColumn",JoinKind.LeftOuter),
 #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Value"}, {"Value.1"}),
 #"Inserted Sum" = Table.AddColumn(#"Expanded NewColumn", "Sum", each List.Sum({[Value], [Value.1]}), type number),
 #"Removed Columns" = Table.RemoveColumns(#"Inserted Sum",{"Value", "Value.1"}),
 #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Attribute]), "Attribute", "Sum", List.Sum)
in
 #"Pivoted Column"

There you see that I've added some other cases in the source-data that this solution caters for (actually, this is new code here, so just skip the previous one I've posted).

[trevb]

Thanks @ImkeF, 

I figured out the leading space after a bit 😉  and I learnt a lot from this code.  Many thanks for your help with this it's been really useful.

@trevb