Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi
I need create a table using power query, based on a Pivoted Table with more than 3 columns. The new table must have 3 columns, and the rows from remaining columns of Pivoted table must be moved under new 3 columns.
See picture attached.
Solved! Go to Solution.
Hi @arcejames ,
if you're looking for a dynamic solution, you can use this approach:
// Query1
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUYoA4oKcxOKSzGQgyxCIk/PLUouAdCIQl1QWpBoqxepgU2sEks8vAJJJUJVGYJVOQF4kEOemliTmAGljEDszJSUnFcgAIZiiKCAuKk1KAtuGQLGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Type = _t, Material1 = _t, Description1 = _t, Materia2 = _t, Description2 = _t, Material3 = _t, Description3 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}, {"Material1", type text}, {"Description1", type text}, {"Materia2", Int64.Type}, {"Description2", type text}, {"Material3", type text}, {"Description3", type text}}),
SplitToColumns = List.Skip(Table.ToColumns(#"Changed Type")),
Materials = List.Union(List.Alternate(SplitToColumns,1,1,1)),
Descriptions = List.Union(List.Alternate(SplitToColumns,1,1)),
NoOfColumnStacks = (Table.ColumnCount(Source) - 1 ) / 2,
CreateTable = Table.FromColumns({List.Repeat(Source[Type], NoOfColumnStacks), Materials, Descriptions}),
ReplaceBlankByNull = Table.ReplaceValue(CreateTable,"",null,Replacer.ReplaceValue,{"Column2", "Column3"}),
FilterOutNulls = Table.SelectRows(ReplaceBlankByNull, each ([Column2] <> null) and ([Column3] <> null))
in
FilterOutNulls
It also allocates the Types from the first column to the other rows - hope that's OK.
It is a variation of the approach I've publishd here, if you're interested in how it works: https://www.thebiccountant.com/2019/02/28/unstacking-columns-in-power-bi-power-query-excel/
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
a slightly different solution to stack the columns, based (actually, doing the reverse) on @ImkeF suggestions at
https://www.thebiccountant.com/2019/02/28/unstacking-columns-in-power-bi-power-query-excel/
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUaoA4oKCAiBpCMTJyclAMhGISwyVYnXQlRiBJEpKgGQSiGUEVuIEZFYCcW5uLpA0BrEyU4AkCMHkq4C4qKgIKgqViwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"type" = _t, m1 = _t, d1 = _t, m2 = _t, d2 = _t, m3 = _t, d3 = _t]),
ct = Table.TransformColumnTypes(Source,{{"type", type text}, {"m1", type text}, {"d1", type text}, {"m2", Int64.Type}, {"d2", type text}, {"m3", type text}, {"d3", type text}}),
ttc=Table.ToColumns(ct),
cols=Table.ColumnNames(ct),
stack=(SCgrpCols)=> List.Combine(Table.ToColumns(Table.SelectColumns(ct,List.Select(cols, each Text.StartsWith(_,SCgrpCols)))))
in
Table.FromColumns({ttc{0},stack("m"),stack("d")},{"type","m","d"})
after a bit of makeup (after mash up)
let
#"Query1 (2)" = let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUaoA4oKCAiBpCMTJyclAMhGISwyVYnXQlRiBJEpKgGQSiGUEVuIEZFYCcW5uLpA0BrEyU4AkCMHkq4C4qKgIKgqViwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"type" = _t, m1 = _t, d1 = _t, m2 = _t, d2 = _t, m3 = _t, d3 = _t]),
ct = Table.TransformColumnTypes(Source,{{"type", type text}, {"m1", type text}, {"d1", type text}, {"m2", Int64.Type}, {"d2", type text}, {"m3", type text}, {"d3", type text}}),
ttc=Table.ToColumns(ct),
cols=Table.ColumnNames(ct),
stack=(grpCols)=> List.Combine(Table.ToColumns(Table.SelectColumns(ct,List.Select(cols, each Text.StartsWith(_,grpCols)))))
in
Table.FromColumns({List.Combine(List.Repeat({ttc{0}},3)),stack("m"),stack("d")},{"type","m","d"}),
#"Removed Blank Rows" = Table.SelectRows(#"Query1 (2)", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_[[m],[d]]), {"", null})))
in
#"Removed Blank Rows"
a somewhat crude solution ...
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUaoA4oKCAiBpCMTJyclAMhGISwyVYnXQlRiBJEpKgGQSiGUEVuIEZFYCcW5uLpA0BrEyU4AkCMHkq4C4qKgIKgqViwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"type" = _t, m1 = _t, d1 = _t, m2 = _t, d2 = _t, m3 = _t, d3 = _t]),
ct = Table.TransformColumnTypes(Source,{{"type", type text}, {"m1", type text}, {"d1", type text}, {"m2", Int64.Type}, {"d2", type text}, {"m3", type text}, {"d3", type text}}),
ttc=Table.ToColumns(ct),
lt=List.Accumulate({0..2},{{},{}},(s,c)=>{s{0}&ttc{2*c+1} , s{1}& ttc{2*(c+1)}} ),
tfc=Table.FromColumns({ttc{0}}<)
in
Table.SelectRows(tfc, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))
a slightly different solution to stack the columns, based (actually, doing the reverse) on @ImkeF suggestions at
https://www.thebiccountant.com/2019/02/28/unstacking-columns-in-power-bi-power-query-excel/
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUaoA4oKCAiBpCMTJyclAMhGISwyVYnXQlRiBJEpKgGQSiGUEVuIEZFYCcW5uLpA0BrEyU4AkCMHkq4C4qKgIKgqViwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"type" = _t, m1 = _t, d1 = _t, m2 = _t, d2 = _t, m3 = _t, d3 = _t]),
ct = Table.TransformColumnTypes(Source,{{"type", type text}, {"m1", type text}, {"d1", type text}, {"m2", Int64.Type}, {"d2", type text}, {"m3", type text}, {"d3", type text}}),
ttc=Table.ToColumns(ct),
cols=Table.ColumnNames(ct),
stack=(SCgrpCols)=> List.Combine(Table.ToColumns(Table.SelectColumns(ct,List.Select(cols, each Text.StartsWith(_,SCgrpCols)))))
in
Table.FromColumns({ttc{0},stack("m"),stack("d")},{"type","m","d"})
after a bit of makeup (after mash up)
let
#"Query1 (2)" = let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUaoA4oKCAiBpCMTJyclAMhGISwyVYnXQlRiBJEpKgGQSiGUEVuIEZFYCcW5uLpA0BrEyU4AkCMHkq4C4qKgIKgqViwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"type" = _t, m1 = _t, d1 = _t, m2 = _t, d2 = _t, m3 = _t, d3 = _t]),
ct = Table.TransformColumnTypes(Source,{{"type", type text}, {"m1", type text}, {"d1", type text}, {"m2", Int64.Type}, {"d2", type text}, {"m3", type text}, {"d3", type text}}),
ttc=Table.ToColumns(ct),
cols=Table.ColumnNames(ct),
stack=(grpCols)=> List.Combine(Table.ToColumns(Table.SelectColumns(ct,List.Select(cols, each Text.StartsWith(_,grpCols)))))
in
Table.FromColumns({List.Combine(List.Repeat({ttc{0}},3)),stack("m"),stack("d")},{"type","m","d"}),
#"Removed Blank Rows" = Table.SelectRows(#"Query1 (2)", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_[[m],[d]]), {"", null})))
in
#"Removed Blank Rows"
@arcejames It's not elegant, but you could do it like the following:
Table (5)
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUYoA4oKcxOKSzGQgyxCIk/PLUouAdCIQl1QWpBoqxepgU2sEks8vAJJJUJVGYJVOQF4kEOemliTmAGljEDszJSUnFcgAIZiiKCAuKk1KAtuGQLGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Type = _t, Material1 = _t, Description1 = _t, Materia2 = _t, Description2 = _t, Material3 = _t, Description3 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}, {"Material1", type text}, {"Description1", type text}, {"Materia2", Int64.Type}, {"Description2", type text}, {"Material3", type text}, {"Description3", type text}})
in
#"Changed Type"
Table (6)
let
Source = #"Table (5)",
#"Removed Columns" = Table.RemoveColumns(Source,{"Materia2", "Description2", "Material3", "Description3"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Material1", "Material"}, {"Description1", "Description"}})
in
#"Renamed Columns"
Table (7)
let
Source = #"Table (5)",
#"Removed Columns" = Table.RemoveColumns(Source,{"Material1", "Description1", "Material3", "Description3"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Materia2", "Material"}, {"Description2", "Description"}})
in
#"Renamed Columns"
Table (8)
let
Source = #"Table (5)",
#"Removed Columns" = Table.RemoveColumns(Source,{"Material1", "Description1", "Materia2", "Description2"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Material3", "Material"}, {"Description3", "Description"}})
in
#"Renamed Columns"
Append1
let
Source = Table.Combine({#"Table (6)", #"Table (7)", #"Table (8)"}),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Material", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Material] <> null and [Material] <> ""))
in
#"Filtered Rows"
PBIX is attached below sig.
@arcejames - Here it is as a single query:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUYoA4oKcxOKSzGQgyxCIk/PLUouAdCIQl1QWpBoqxepgU2sEks8vAJJJUJVGYJVOQF4kEOemliTmAGljEDszJSUnFcgAIZiiKCAuKk1KAtuGQLGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Type = _t, Material1 = _t, Description1 = _t, Materia2 = _t, Description2 = _t, Material3 = _t, Description3 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}, {"Material1", type text}, {"Description1", type text}, {"Materia2", Int64.Type}, {"Description2", type text}, {"Material3", type text}, {"Description3", type text}}),
#"Removed Columns1" = Table.RemoveColumns(Source,{"Materia2", "Description2", "Material3", "Description3"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Columns1",{{"Material1", "Material"}, {"Description1", "Description"}}),
#"Removed Columns2" = Table.RemoveColumns(Source,{"Material1", "Description1", "Material3", "Description3"}),
#"Renamed Columns2" = Table.RenameColumns(#"Removed Columns2",{{"Materia2", "Material"}, {"Description2", "Description"}}),
#"Removed Columns3" = Table.RemoveColumns(Source,{"Material1", "Description1", "Materia2", "Description2"}),
#"Renamed Columns3" = Table.RenameColumns(#"Removed Columns3",{{"Material3", "Material"}, {"Description3", "Description"}}),
#"Append1" = Table.Combine({#"Renamed Columns1", #"Renamed Columns2", #"Renamed Columns3"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Append1",{{"Material", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([Material] <> null and [Material] <> ""))
in
#"Filtered Rows"
@ImkeF or @edhans might have something better.
Hi @arcejames ,
if you're looking for a dynamic solution, you can use this approach:
// Query1
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUYoA4oKcxOKSzGQgyxCIk/PLUouAdCIQl1QWpBoqxepgU2sEks8vAJJJUJVGYJVOQF4kEOemliTmAGljEDszJSUnFcgAIZiiKCAuKk1KAtuGQLGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Type = _t, Material1 = _t, Description1 = _t, Materia2 = _t, Description2 = _t, Material3 = _t, Description3 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}, {"Material1", type text}, {"Description1", type text}, {"Materia2", Int64.Type}, {"Description2", type text}, {"Material3", type text}, {"Description3", type text}}),
SplitToColumns = List.Skip(Table.ToColumns(#"Changed Type")),
Materials = List.Union(List.Alternate(SplitToColumns,1,1,1)),
Descriptions = List.Union(List.Alternate(SplitToColumns,1,1)),
NoOfColumnStacks = (Table.ColumnCount(Source) - 1 ) / 2,
CreateTable = Table.FromColumns({List.Repeat(Source[Type], NoOfColumnStacks), Materials, Descriptions}),
ReplaceBlankByNull = Table.ReplaceValue(CreateTable,"",null,Replacer.ReplaceValue,{"Column2", "Column3"}),
FilterOutNulls = Table.SelectRows(ReplaceBlankByNull, each ([Column2] <> null) and ([Column3] <> null))
in
FilterOutNulls
It also allocates the Types from the first column to the other rows - hope that's OK.
It is a variation of the approach I've publishd here, if you're interested in how it works: https://www.thebiccountant.com/2019/02/28/unstacking-columns-in-power-bi-power-query-excel/
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
101 | |
50 | |
19 | |
12 | |
11 |