Forum Discussion

arcejames's avatar
arcejames
Frequent Visitor
5 years ago
Solved

Create a Table from existing columns

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.

Need Help
  • ImkeF's avatar
    ImkeF
    5 years ago

    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/ 

     

  • Anonymous's avatar
    Anonymous
    5 years ago

    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"

     

     

     

5 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    5 years ago

    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.

     

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      5 years ago

      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.

      • ImkeF's avatar
        ImkeF
        Community Champion
        5 years ago

        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/ 

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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}}&lt)
in   
Table.SelectRows(tfc, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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"