Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Pivot/ transpose

Hi Everyone,   This is my first post and not sure if this is the correct way to post questions and request help.  I am trying to traspose/Pivot data in Power Query, Below is what I am trying to do...
Need Help
  • ziying35's avatar
    ziying35
    6 years ago

    Anonymous 

    generic function:

     

    // output
let
    Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("i65WcvZ3cVWyUnIyMDBU0lEKTyxLVbIy1FFKSSwBMpS8SnMUDBWMDIwMgJLK+WmleZklxUpWprU62HUaoeo0IEGrMVyrY2m6ghGmTkMDVK1GONxrTNBSIxzuNbIkxVaEg4NTCxQMsbgYp7UmcK3+ySXY/Iqm0xiLV30TK4H6MLWa4dKK7Nc8BXMsXrWojQUA",BinaryEncoding.Base64),Compression.Deflate))),
    result = fnTrans(Source, {"CODE"})
in
    result

// fnTrans
(sTbl as table, groupList as list)=>
let
      fn=(tbl as table)=>
      let
          fd = List.Skip(Table.ColumnNames(tbl)),
          nfd = groupList&List.TransformMany({1..Table.RowCount(tbl)}, each fd, (x,y)=>y&Text.From(x))
      in  Table.FromRows({Record.ToList((Table.SelectColumns(tbl, groupList)){0})&List.Combine(Table.ToRows(Table.SelectColumns(tbl, fd)))}, nfd),
    group = Table.Group(sTbl, groupList, {"t", fn}, 0)[t],
    result = Table.Combine(group)
in
    result
  • artemus's avatar
    artemus
    6 years ago

    Here is another generic function

     

    Value.ReplaceType((table as table) =>
    let 
        combineColumnName = Text.NewGuid()
    in
    Value.ReplaceType((primaryAttributeColumn as text, subAttributeColumns as list) as function =>
        Value.ReplaceType((primaryAttributeValues as list) =>
            let
                primaryValueColumnNameFn = each primaryAttributeColumn & "." & Text.From(_),
                primaryValueColumnNames = List.Transform(primaryAttributeValues, primaryValueColumnNameFn),
                attributeColumnToText = Table.TransformColumns(table, {primaryAttributeColumn, primaryValueColumnNameFn, type text}),
                mergeNewColumns = Table.CombineColumns(attributeColumnToText, subAttributeColumns, Value.ReplaceType(each Record.FromList(_, subAttributeColumns), type function(values as list) as Type.TableRow(Value.Type(Table.SelectColumns(#table(Value.Type(attributeColumnToText), {}), subAttributeColumns)))), combineColumnName),
                pivot = Table.Pivot(mergeNewColumns, primaryValueColumnNames, primaryAttributeColumn, combineColumnName),
                expandPivotColumn = List.Accumulate(primaryValueColumnNames, pivot, (t, c) => Table.ExpandRecordColumn(t, c, subAttributeColumns, List.Transform(subAttributeColumns, each c & "." & _)))
            in
                expandPivotColumn,
            type function (primaryAttributeValues as (type {text} meta [Documentation.AllowedValues = List.Distinct(Table.Column(table, primaryAttributeColumn))])) as {text} meta [Documentation.Description = "Pivot Multiple (Step 3)", Documentation.LongDescription = "Step 3: Confirm the primary attribute values"]),
        type function
        (
            primaryAttributeColumn as (type text meta [Documentation.AllowedValues = Table.ColumnNames(table)]),
            subAttributeColumns as (type {text} meta [Documentation.AllowedValues = Table.ColumnNames(table)])
        ) as function meta [Documentation.Description = "Pivot Multiple (Step 2)", Documentation.LongDescription = "Step 2: Choose the primary attribute, and the list of sub attributes"]
    ),
    (type function (table as table) as table) meta [Documentation.Description = "Pivot Multiple", Documentation.LongDescription = "Step 1: Choose a table to perform a multiple Pivot"]
)
artemus's avatar
artemus
Microsoft Employee
6 years ago

I can make a generic "PivotMultiple" function if anyone wants that.

 

The issue is that you want a stable schema that doesn't change base on the data

edhans's avatar
edhans
Community Champion
6 years ago

Yes, no doubt artemus - Just wanted Anonymous to know your solution as provided was a single shot, not dynamic. Not questioning your ability to make it dynamic. ðŸ˜‰ Although, it could end with a solution with thousands of columns as the data grew.

Just trying to understand the end goal and the best way to do the solution. The bridge table, which BA_Pete provided an example of, is, I think, the best long term solution given what we know so far.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Thank you edhans for your advice! I totally agree with all you've mentioned.

    I am trying to have a quick solution, which did happen for the time being.

    It would be great to have a generic "Pivot" function which would totally solve my issue as I do not expect the number of waves to increase alot. 

     

    Thank you again,

    Assadi 

    • ziying35's avatar
      ziying35
      Impactful Individual
      6 years ago

      Anonymous 

      generic function:

       

      // output
let
    Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("i65WcvZ3cVWyUnIyMDBU0lEKTyxLVbIy1FFKSSwBMpS8SnMUDBWMDIwMgJLK+WmleZklxUpWprU62HUaoeo0IEGrMVyrY2m6ghGmTkMDVK1GONxrTNBSIxzuNbIkxVaEg4NTCxQMsbgYp7UmcK3+ySXY/Iqm0xiLV30TK4H6MLWa4dKK7Nc8BXMsXrWojQUA",BinaryEncoding.Base64),Compression.Deflate))),
    result = fnTrans(Source, {"CODE"})
in
    result

// fnTrans
(sTbl as table, groupList as list)=>
let
      fn=(tbl as table)=>
      let
          fd = List.Skip(Table.ColumnNames(tbl)),
          nfd = groupList&List.TransformMany({1..Table.RowCount(tbl)}, each fd, (x,y)=>y&Text.From(x))
      in  Table.FromRows({Record.ToList((Table.SelectColumns(tbl, groupList)){0})&List.Combine(Table.ToRows(Table.SelectColumns(tbl, fd)))}, nfd),
    group = Table.Group(sTbl, groupList, {"t", fn}, 0)[t],
    result = Table.Combine(group)
in
    result
      • artemus's avatar
        artemus
        Microsoft Employee
        6 years ago

        Here is another generic function

         

        Value.ReplaceType((table as table) =>
    let 
        combineColumnName = Text.NewGuid()
    in
    Value.ReplaceType((primaryAttributeColumn as text, subAttributeColumns as list) as function =>
        Value.ReplaceType((primaryAttributeValues as list) =>
            let
                primaryValueColumnNameFn = each primaryAttributeColumn & "." & Text.From(_),
                primaryValueColumnNames = List.Transform(primaryAttributeValues, primaryValueColumnNameFn),
                attributeColumnToText = Table.TransformColumns(table, {primaryAttributeColumn, primaryValueColumnNameFn, type text}),
                mergeNewColumns = Table.CombineColumns(attributeColumnToText, subAttributeColumns, Value.ReplaceType(each Record.FromList(_, subAttributeColumns), type function(values as list) as Type.TableRow(Value.Type(Table.SelectColumns(#table(Value.Type(attributeColumnToText), {}), subAttributeColumns)))), combineColumnName),
                pivot = Table.Pivot(mergeNewColumns, primaryValueColumnNames, primaryAttributeColumn, combineColumnName),
                expandPivotColumn = List.Accumulate(primaryValueColumnNames, pivot, (t, c) => Table.ExpandRecordColumn(t, c, subAttributeColumns, List.Transform(subAttributeColumns, each c & "." & _)))
            in
                expandPivotColumn,
            type function (primaryAttributeValues as (type {text} meta [Documentation.AllowedValues = List.Distinct(Table.Column(table, primaryAttributeColumn))])) as {text} meta [Documentation.Description = "Pivot Multiple (Step 3)", Documentation.LongDescription = "Step 3: Confirm the primary attribute values"]),
        type function
        (
            primaryAttributeColumn as (type text meta [Documentation.AllowedValues = Table.ColumnNames(table)]),
            subAttributeColumns as (type {text} meta [Documentation.AllowedValues = Table.ColumnNames(table)])
        ) as function meta [Documentation.Description = "Pivot Multiple (Step 2)", Documentation.LongDescription = "Step 2: Choose the primary attribute, and the list of sub attributes"]
    ),
    (type function (table as table) as table) meta [Documentation.Description = "Pivot Multiple", Documentation.LongDescription = "Step 1: Choose a table to perform a multiple Pivot"]
)