Forum Discussion
Advocate IISome ideas for shaping my data.
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 :-)
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).
6 Replies
- Greg_Deckler
Community Champion
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.
