Forum Discussion
Anonymous
10 years agoNot applicable
Unpivot Multiple Sections of Data
Hi All, I've got a situation that I cannot figure out that I'm hoping someone else has run into. I've got a table of sales information with months across the columns. Usually this would be a qui...
Anonymous
10 years agoNot applicable
Greg_Deckler ImkeF You guys are awesome! As soon as I'm back in the office I'm giving this a go! This is M correct? Where'd you guys learn all of this? I'm still trying to learn all of the DAX commands. Any tips or resources would be awesome.
Greg_Deckler
Community Champion
10 years agoYes, this is M code. I started learning M by using the query editor and then switching to View | Advanced and looking at the code. Then you can use references like:
M: https://msdn.microsoft.com/library/Mt253322?ui=en-US&rs=en-US&ad=US
DAX: https://msdn.microsoft.com/en-us/library/ee634396.aspx?f=255&MSPPError=-2147217396
I'm in Chrome on this session so let's see if the code block works now for pasting:
let
// comment
Source1 = Csv.Document(File.Contents("C:\temp\powerbi\pivot.csv"),[Delimiter=",", Encoding=1252]),
Source = Csv.Document(File.Contents("C:\temp\powerbi\pivot.csv"),[Delimiter=",", Encoding=1252]),
#"Promoted Headers1" = Table.PromoteHeaders(Source),
#"Removed Columns1" = Table.RemoveColumns(#"Promoted Headers",{"April_$", "May_$", "June_$"}),
#"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Removed Columns", {"Product", "Customer"}, "Attribute", "Value"),
#"Changed Type1" = Table.TransformColumnTypes(#"Unpivoted Columns1",{{"Customer", type text}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type1", "Merge", each [Product] & [Customer] & List.First(Text.Split([Attribute],"_"))),
#"Promoted Headers" = Table.PromoteHeaders(Source),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Product", type text}, {"Customer", Int64.Type}, {"April_Qty", Int64.Type}, {"May_Qty", Int64.Type}, {"June_Qty", Int64.Type}, {"April_$", Int64.Type}, {"May_$", Int64.Type}, {"June_$", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"April_Qty", "May_Qty", "June_Qty"}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Product", "Customer"}, "Attribute", "Value"),
#"Changed Type2" = Table.TransformColumnTypes(#"Unpivoted Columns",{{"Customer", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type2", "Merge", each [Product] & [Customer] & List.First(Text.Split([Attribute],"_"))),
#"Merged Queries" = Table.NestedJoin(#"Added Custom",{"Merge"},#"Added Custom1",{"Merge"},"NewColumn",JoinKind.LeftOuter),
#"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Value"}, {"NewColumn.Value"}),
#"Removed Columns2" = Table.RemoveColumns(#"Expanded NewColumn",{"Merge"})
in
#"Removed Columns2"