Pivot/ transpose

[Anonymous]

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:

Current Table:

CODE	Wave	date	#ofunits
B001	1	Jul 1 2020	5
B001	2	Jul 10 2020	5
B001	3	Aug 2 2020	10
B002	1	Jul 3 2020	5
B002	2	Jul 29 2020	10
B002	3	Sep 12 2020	5
B002	4	Oct 2 2020	5
B003	1	May 20 2020	6
B003	2	Jun 7 2020	18

 I would like to change this table to the following:

Requested Table:

CODE	Wave	date	#ofunits	Wave2	date2	#ofunits2	Wave3	date3	#ofunits3	Wave4	date4	#ofunits4
B001	1	Jul 1 2020	5	2	Jul 10 2020	5	3	Aug 2 2020	10
B002	1	Jul 3 2020	5	2	Jul 29 2020	10	3	Sep 12 2020	5	4	Oct 2 2020	5
B003	1	May 20 2020	6	2	Jun 7 2020	18

Thank you in advance for your support!

[ziying35 · Accepted solution]

@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

[ziying35]

@Anonymous 

let
    Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("i65WcvZ3cVWyUnIyMDBU0lEKTyxLVbIy1FFKSSwBMpS8SnMUDBWMDIwMgJLK+WmleZklxUpWprU62HUaoeo0IEGrMVyrY2m6ghGmTkMDVK1GONxrTNBSIxzuNbIkxVaEg4NTCxQMsbgYp7UmcK3+ySXY/Iqm0xiLV30TK4H6MLWa4dKK7Nc8BXMsXrWojQUA",BinaryEncoding.Base64),Compression.Deflate))),
    fn=(tbl as table)=>
      let
          fd = List.Skip(Table.ColumnNames(tbl)),
          nfd = {"CODE"}&List.TransformMany({1..Table.RowCount(tbl)}, each fd, (x,y)=>y&Text.From(x))
      in  Table.FromRows({{tbl[CODE]{0}}&List.Combine(Table.ToRows(Table.SelectColumns(tbl, fd)))}, nfd),
    group = Table.Group(Source, "CODE", {"t", fn}, 0)[t],
    result = Table.Combine(group)
in
    result

[artemus]

Here it will look like this (note I left off wave columns as they are all the same for each row):

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjIwMFTSUQJhr9IcBUMFIwMjAyDHVClWBy5rBJM1wCptDMSOpekKRjBZQwOYtBGS2cYYmo2QzDayxK4bZHhwaoGCoRFW7SZA7J9cooApawy12jexEigHkzZDlobYnadgDrfaQik2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [CODE = _t, Wave = _t, date = _t, #"#ofunits" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CODE", type text}, {"Wave", Int64.Type}, {"date", type date}, {"#ofunits", Int64.Type}}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type", {{"date", type text}, {"#ofunits", type text}}, "en-US"),{"date", "#ofunits"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged"),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Merged Columns", {{"Wave", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Merged Columns", {{"Wave", type text}}, "en-US")[Wave]), "Wave", "Merged"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Pivoted Column", "1", Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.Csv, false), {"date1", "#units1"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "2", Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.Csv, false), {"date2", "#units2"}),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Split Column by Delimiter1", "3", Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.Csv, false), {"date3", "#units3"}),
    #"Split Column by Delimiter3" = Table.SplitColumn(#"Split Column by Delimiter2", "4", Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.Csv, false), {"date4", "#units4"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter3",{{"date1", type date}, {"#units1", Int64.Type}, {"date2", type date}, {"#units2", Int64.Type}, {"date3", type date}, {"#units3", Int64.Type}, {"date4", type date}, {"#units4", Int64.Type}})
in
    #"Changed Type1"

[edhans]

Can I ask why you are doing this? This is backwards to normal transformations. Your first table is normalized, which is what Power BI needs to work efficiently.

Can you give us a hint of your end goal? Using DAX to analyze the desired format is going to be a pain. Using it to analyze the first will be a piece of cake.

[Anonymous]

Thank you for the quick response!

I wanted to have this table as unique table (on the column CODE) as I am trying to merge/link these columns to another table that is also unique. I do not want the other table to have duplicate values for the code column. 

Hope I explained it properly.

[edhans]

I would be inclined to create a bridge table. See many-to-many guidance here. The solution @artemus provided works for this exact data set and is a clever approach, but it will not work if you add more data that requires more columns.

I do not think de-normalizing your data to get a unique field is the right approach for a sustainable and flexible solution.

[artemus]

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

[Anonymous]

Thank you @artemus!

Your Solution worked and I was able to create my requested table. As mentioned earlier by @edhans this is for a static data set. would it be difficult to create  generic "PivotMultiple" function as you've mentioned. That would be really useful!

I would like to close this as a solved solution but will you be able to share the generic "PivotMultiple" function (if you decided to do so ) even if I closed the post?

Thank you,

[edhans]

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]

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 · Accepted solution]

@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 · Accepted solution]

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"]
)

[BA_Pete]

Hi @Anonymous ,

It sounds like you need a dimension table. Create a table that contains only unique values of [Code], then relate this to your other tables that do not have unique values.

One way you can create a dimension table manually in Power Query is by using Enter Data, or you can do this is the data model by creating a new table with the following formula, but there are actually many ways to achieve this:

code = SUMMARIZE(aTable, aTable[code])

Then relate the tables as follows:

This will allow you to use both tables' data in calculations and visualisations using the dimension table as a common field between the two.

Pete