Forum Discussion
Help With Cross data
Hi Power bi Community
I need some help, How can solve this by dax? I need to create a measure where find the values in yellow, and put it in a column, crossing row month with column month
thank you very much
*********************************************************************************************************
let
Source = Excel.Workbook(File.Contents("Enter your data source path here"), null, true),
Base_Sheet = Source{[Item="Base",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Base_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"mont year", type text}, {"mon mun", Int64.Type}, {"JAN", Int64.Type}, {"FEB", Int64.Type}, {"MAR", Int64.Type}, {"APR", Int64.Type}, {"MAY", Int64.Type}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"mont year", "mon mun"}, "Atribute", "Value"),
#"Grouped Rows" = Table.Group(#"Unpivoted Columns", {"mon mun"}, {{"Group", each _, type table [mont year=nullable text, mon mun=nullable number, Atributo=text, Valor=number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "TableList", each Table.AddIndexColumn([Group],"Index",1,1)),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"TableList"}),
#"Expanded {0}" = Table.ExpandTableColumn(#"Removed Other Columns", "TableList", {"mont year", "mon mun", "Atribute", "Value", "Index"}, {"mont year", "mon mun", "Atribute", "Value", "Index"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded {0}",{{"mont year", type text}, {"mon mun", Int64.Type}, {"Atribute", type text}, {"Value", Int64.Type}, {"Index", Int64.Type}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type1", "Action", each if [mon mun] = [Index] then "Keep" else "Remove"),
#"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each ([Action] = "Keep"))
in
#"Filtered Rows"
*********************************************************************************************************
Did I solve your problem?
Please mark as solution so others can find this solution.
https://www.linkedin.com/in/rodrigosanpbi/Hi,
In the Query Editor, remove the mon mun column. Right click on the first column and select "Unpivot Other Columns". Write an If function then to check whether the first column = attribute column. Filter this new column on true.
9 Replies
This deal can be done in Power Query directly
Thanks, RodrigoSan
how I could do that?*********************************************************************************************************
let
Source = Excel.Workbook(File.Contents("Enter your data source path here"), null, true),
Base_Sheet = Source{[Item="Base",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Base_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"mont year", type text}, {"mon mun", Int64.Type}, {"JAN", Int64.Type}, {"FEB", Int64.Type}, {"MAR", Int64.Type}, {"APR", Int64.Type}, {"MAY", Int64.Type}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"mont year", "mon mun"}, "Atribute", "Value"),
#"Grouped Rows" = Table.Group(#"Unpivoted Columns", {"mon mun"}, {{"Group", each _, type table [mont year=nullable text, mon mun=nullable number, Atributo=text, Valor=number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "TableList", each Table.AddIndexColumn([Group],"Index",1,1)),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"TableList"}),
#"Expanded {0}" = Table.ExpandTableColumn(#"Removed Other Columns", "TableList", {"mont year", "mon mun", "Atribute", "Value", "Index"}, {"mont year", "mon mun", "Atribute", "Value", "Index"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded {0}",{{"mont year", type text}, {"mon mun", Int64.Type}, {"Atribute", type text}, {"Value", Int64.Type}, {"Index", Int64.Type}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type1", "Action", each if [mon mun] = [Index] then "Keep" else "Remove"),
#"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each ([Action] = "Keep"))
in
#"Filtered Rows"
*********************************************************************************************************
Did I solve your problem?
Please mark as solution so others can find this solution.
https://www.linkedin.com/in/rodrigosanpbi/
Hi,
How is your source data table arranged? Share that in a format that can be pasted in an MS Excel file. Also, would you be OK with an M language solution as well?
Hi Ashish!
Thanks for reply
It is in that right format in excel, I'm not very skilled with M language, but I´m open to a solution over M.
Thank you very muchHi,
In the Query Editor, remove the mon mun column. Right click on the first column and select "Unpivot Other Columns". Write an If function then to check whether the first column = attribute column. Filter this new column on true.
