Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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:

 

Current Table:

 

CODEWavedate#ofunits
B0011Jul 1 20205
B0012Jul 10 20205
B0013Aug 2 202010
B0021Jul 3 20205
B0022Jul 29 202010
B0023Sep 12 20205
B0024Oct 2 20205
B0031May 20 20206
B0032Jun 7 202018

 

 I would like to change this table to the following:

Requested Table:

CODEWavedate#ofunitsWave2date2#ofunits2Wave3date3#ofunits3Wave4date4#ofunits4
B0011Jul 1 202052Jul 10 202053Aug 2 202010   
B0021Jul 3 202052Jul 29 2020103Sep 12 202054Oct 2 20205
B0031May 20 202062Jun 7 202018      

 

Thank you in advance for your support!

 

2 ACCEPTED SOLUTIONS
ziying35
Impactful Individual
Impactful Individual

@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

View solution in original post

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

View solution in original post

12 REPLIES 12
ziying35
Impactful Individual
Impactful Individual

@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
Employee
Employee

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
Super User
Super User

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

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.

 

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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
Not applicable

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,

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

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
Impactful Individual
Impactful Individual

@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

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

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:

assadi.PNG

 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors