cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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

Accepted Solutions
Highlighted
Solution Supplier
Solution Supplier

Re: Pivot/ transpose

@Assadi 

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

Highlighted
Microsoft
Microsoft

Re: Pivot/ transpose

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

Re: Pivot/ transpose

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
Highlighted
Microsoft
Microsoft

Re: Pivot/ transpose

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"

 

Highlighted
Frequent Visitor

Re: Pivot/ transpose

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.

 

Highlighted
Super User I
Super User I

Re: Pivot/ transpose

Hi @Assadi ,

 

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

Highlighted
Super User III
Super User III

Re: Pivot/ transpose

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
Highlighted
Microsoft
Microsoft

Re: Pivot/ transpose

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

Highlighted
Super User III
Super User III

Re: Pivot/ transpose

Yes, no doubt @artemus - Just wanted @Assadi 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
Highlighted
Frequent Visitor

Re: Pivot/ transpose

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,

Highlighted
Frequent Visitor

Re: Pivot/ transpose

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 

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors