Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to 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
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"]
)
@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
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"
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThank 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingI 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
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThank 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
@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:
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
Proud to be a Datanaut!