Forum Discussion
Solved
Need help combining data
Hi,
In need help combing some data. I have been trying all morning, but I cannot figure it out. I just started with Power BI, so hopefully there is a simple solution.
I have the following table (file: Excel File):
What I want is a graph per project with the planned hours per year (bar) and the realised hours per year (line). I want this to be cumulative (so 2013 = 2012+2013, 2014 = 2012 + 2013 + 2014 etc.). And I slicer so I can choose the project to show the graph for. Like this:
The problem I am facing is that either Project names or the years end up on the top row/headers. But I want to use both as a filter (not sure that is the right term).
Thanks!
Basically unpivot will help
I posted a similar solution here
http://community.powerbi.com/t5/Desktop/Actual-versus-projected/m-p/348465#M156474
I split the data into 2 querys on both unpivot the years.
Create a key of project and year and then merge back together
Here are the code from the Advanced Queries.
Projects
let
Source = Excel.Workbook(File.Contents("L:\Downloads\example-file.xlsx"), null, true),
Blad1_Sheet = Source{[Item="Blad1",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Blad1_Sheet,{{"Column1", type text}, {"Column2", type any}, {"Column3", Int64.Type}, {"Column4", Int64.Type}, {"Column5", Int64.Type}, {"Column6", Int64.Type}, {"Column7", Int64.Type}, {"Column8", Int64.Type}, {"Column9", type any}, {"Column10", Int64.Type}, {"Column11", Int64.Type}, {"Column12", Int64.Type}, {"Column13", type number}, {"Column14", Int64.Type}, {"Column15", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15"}),
#"Removed Top Rows" = Table.Skip(#"Removed Columns",1),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Projectname", type text}, {"Total", Int64.Type}, {"2012", Int64.Type}, {"2013", Int64.Type}, {"2014", Int64.Type}, {"2015", Int64.Type}, {"2016", Int64.Type}, {"2017", Int64.Type}}),
#"Removed Bottom Rows" = Table.RemoveLastN(#"Changed Type1",5),
#"Removed Columns1" = Table.RemoveColumns(#"Removed Bottom Rows",{"Total"}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns1", {"Projectname"}, "Attribute", "Value"),
#"Duplicated Column" = Table.DuplicateColumn(#"Unpivoted Columns", "Projectname", "Projectname - Copy"),
#"Duplicated Column1" = Table.DuplicateColumn(#"Duplicated Column", "Attribute", "Attribute - Copy"),
#"Merged Columns" = Table.CombineColumns(#"Duplicated Column1",{"Projectname - Copy", "Attribute - Copy"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"ProjectYearKey"),
#"Merged Queries" = Table.NestedJoin(#"Merged Columns",{"ProjectYearKey"},Realised,{"ProjectYearKey"},"Realised",JoinKind.LeftOuter),
#"Expanded Realised" = Table.ExpandTableColumn(#"Merged Queries", "Realised", {"Value"}, {"Realised.Value"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Realised",{{"Attribute", "Year"}, {"Value", "Planned"}, {"Realised.Value", "Realised"}})
in
#"Renamed Columns"Realised
let
Source = Excel.Workbook(File.Contents("L:\Downloads\example-file.xlsx"), null, true),
Blad1_Sheet = Source{[Item="Blad1",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Blad1_Sheet,{{"Column1", type text}, {"Column2", type any}, {"Column3", Int64.Type}, {"Column4", Int64.Type}, {"Column5", Int64.Type}, {"Column6", Int64.Type}, {"Column7", Int64.Type}, {"Column8", Int64.Type}, {"Column9", type any}, {"Column10", Int64.Type}, {"Column11", Int64.Type}, {"Column12", Int64.Type}, {"Column13", type number}, {"Column14", Int64.Type}, {"Column15", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8"}),
#"Removed Top Rows" = Table.Skip(#"Removed Columns",1),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Projectname", type text}, {"Total", type number}, {"2012", Int64.Type}, {"2013", Int64.Type}, {"2014", Int64.Type}, {"2015", type number}, {"2016", Int64.Type}, {"2017", Int64.Type}}),
#"Removed Bottom Rows" = Table.RemoveLastN(#"Changed Type1",5),
#"Removed Columns1" = Table.RemoveColumns(#"Removed Bottom Rows",{"Total"}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns1", {"Projectname"}, "Attribute", "Value"),
#"Duplicated Column" = Table.DuplicateColumn(#"Unpivoted Columns", "Projectname", "Projectname - Copy"),
#"Duplicated Column1" = Table.DuplicateColumn(#"Duplicated Column", "Attribute", "Attribute - Copy"),
#"Merged Columns" = Table.CombineColumns(#"Duplicated Column1",{"Projectname - Copy", "Attribute - Copy"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"ProjectYearKey")
in
#"Merged Columns"
3 Replies
Basically unpivot will help
I posted a similar solution here
http://community.powerbi.com/t5/Desktop/Actual-versus-projected/m-p/348465#M156474
I split the data into 2 querys on both unpivot the years.
Create a key of project and year and then merge back together
Here are the code from the Advanced Queries.
Projects
let
Source = Excel.Workbook(File.Contents("L:\Downloads\example-file.xlsx"), null, true),
Blad1_Sheet = Source{[Item="Blad1",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Blad1_Sheet,{{"Column1", type text}, {"Column2", type any}, {"Column3", Int64.Type}, {"Column4", Int64.Type}, {"Column5", Int64.Type}, {"Column6", Int64.Type}, {"Column7", Int64.Type}, {"Column8", Int64.Type}, {"Column9", type any}, {"Column10", Int64.Type}, {"Column11", Int64.Type}, {"Column12", Int64.Type}, {"Column13", type number}, {"Column14", Int64.Type}, {"Column15", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15"}),
#"Removed Top Rows" = Table.Skip(#"Removed Columns",1),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Projectname", type text}, {"Total", Int64.Type}, {"2012", Int64.Type}, {"2013", Int64.Type}, {"2014", Int64.Type}, {"2015", Int64.Type}, {"2016", Int64.Type}, {"2017", Int64.Type}}),
#"Removed Bottom Rows" = Table.RemoveLastN(#"Changed Type1",5),
#"Removed Columns1" = Table.RemoveColumns(#"Removed Bottom Rows",{"Total"}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns1", {"Projectname"}, "Attribute", "Value"),
#"Duplicated Column" = Table.DuplicateColumn(#"Unpivoted Columns", "Projectname", "Projectname - Copy"),
#"Duplicated Column1" = Table.DuplicateColumn(#"Duplicated Column", "Attribute", "Attribute - Copy"),
#"Merged Columns" = Table.CombineColumns(#"Duplicated Column1",{"Projectname - Copy", "Attribute - Copy"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"ProjectYearKey"),
#"Merged Queries" = Table.NestedJoin(#"Merged Columns",{"ProjectYearKey"},Realised,{"ProjectYearKey"},"Realised",JoinKind.LeftOuter),
#"Expanded Realised" = Table.ExpandTableColumn(#"Merged Queries", "Realised", {"Value"}, {"Realised.Value"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Realised",{{"Attribute", "Year"}, {"Value", "Planned"}, {"Realised.Value", "Realised"}})
in
#"Renamed Columns"Realised
let
Source = Excel.Workbook(File.Contents("L:\Downloads\example-file.xlsx"), null, true),
Blad1_Sheet = Source{[Item="Blad1",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Blad1_Sheet,{{"Column1", type text}, {"Column2", type any}, {"Column3", Int64.Type}, {"Column4", Int64.Type}, {"Column5", Int64.Type}, {"Column6", Int64.Type}, {"Column7", Int64.Type}, {"Column8", Int64.Type}, {"Column9", type any}, {"Column10", Int64.Type}, {"Column11", Int64.Type}, {"Column12", Int64.Type}, {"Column13", type number}, {"Column14", Int64.Type}, {"Column15", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8"}),
#"Removed Top Rows" = Table.Skip(#"Removed Columns",1),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Projectname", type text}, {"Total", type number}, {"2012", Int64.Type}, {"2013", Int64.Type}, {"2014", Int64.Type}, {"2015", type number}, {"2016", Int64.Type}, {"2017", Int64.Type}}),
#"Removed Bottom Rows" = Table.RemoveLastN(#"Changed Type1",5),
#"Removed Columns1" = Table.RemoveColumns(#"Removed Bottom Rows",{"Total"}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns1", {"Projectname"}, "Attribute", "Value"),
#"Duplicated Column" = Table.DuplicateColumn(#"Unpivoted Columns", "Projectname", "Projectname - Copy"),
#"Duplicated Column1" = Table.DuplicateColumn(#"Duplicated Column", "Attribute", "Attribute - Copy"),
#"Merged Columns" = Table.CombineColumns(#"Duplicated Column1",{"Projectname - Copy", "Attribute - Copy"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"ProjectYearKey")
in
#"Merged Columns"Thanks! This works.
How can I show the cumulative values for each project?
I tried:
CUMULATIVE = CALCULATE ( SUM ( Hours[Realised] ); FILTER( ALL ( Hours); Hours[Year] <= MAX( Hours[Years] ) ))But this shows the total of all projects, instead of only the selected one.
Thanks!
---
Found it: need to use ALLSELECTED instead of ALL.
cool. :catvery-happy:
