Help With Cross data

[alfredorhz]

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

[rodrigosan · Accepted solution]

*********************************************************************************************************
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/

[Ashish_Mathur]

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?

[alfredorhz]

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 much

[Ashish_Mathur · Accepted solution]

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. 

[alfredorhz]

Thank you Ashish, I tried this solution, and works too

[Ashish_Mathur]

You are welcome.

[rodrigosan]

This deal can be done in Power Query directly

[alfredorhz]

Thanks, RodrigoSan

how I could do that?

[rodrigosan · Accepted solution]

*********************************************************************************************************
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/

[alfredorhz]

Thanks, RodrigoSan, works perfect