Hi all, May I seek any help for this table transform?IDNameValue1CategoryShipment1ItemA1Price1001ItemB1Price2001ItemC2CategoryDelivery2ItemA2Price150 Desired Output:IDCategoryItemPrice1ShipmentA1001ShipmentB2001ShipmentC 2DeliveryA150Appreciated with your help!

Given your most recent set of data with four columns, you can modify my code to shift the Cost column by 2 to get your desired results. let Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Name", type text}, {"Value", type any}}), //add index column which will retain rows in original order #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type), //custom pivot pivot = fnPivotAll(#"Added Index","Name","Value"), //There will be some extra nulls as a result of above //Each column will be offset by one (1) from the previous column //So we readjust back offsetPrice = Table.FromColumns( Table.ToColumns(Table.SelectColumns(pivot,{"ID","Category","Item"})) & {List.RemoveFirstN(pivot[Price],1) & {null}} & {List.RemoveFirstN(pivot[Cost],2) & {null,null}}, type table[ID=Int64.Type, Category=text, Item=text,Price=Currency.Type,Cost=Currency.Type]), //Now we can fill down the category column and filter out the nulls #"Filled Down" = Table.FillDown(offsetPrice,{"Category"}), #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Item] <> null)), //Merge the Category and Item columns for final report #"Merged Columns" = Table.CombineColumns(#"Filtered Rows",{"Category", "Item"}, Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Category Item") in #"Merged Columns" However, if the number of columns will be dynamic --, then we should write a custom function which shifts the appropriate number of columns, the appropriate amount depending on how many "extra" columns there are.

Pivot Table with multiple same values | Microsoft Fabric Community

13 Replies

smpa01

Community Champion

4 years ago

ngct1112 you can transform it this way

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nRR0lHyS8xNBVJhiTmlqUqxOtFKhkCec2JJanp+USWQGZyRWZCbmlcCl/MsSc0FUo5wgYCizGSQEYYGBuiKnDAUGWEqcsapyAjVKS6pOZllqUAmTA7FKUbITjEFGhALAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ((try Number.From([Value]) otherwise -999) =-999)),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each if [Name]="Category" then [Value] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Filtered Rows1" = Table.SelectRows(#"Filled Down", each ([Name] = "Item")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Name"}),
    #"Merged Columns" = Table.CombineColumns(#"Removed Columns",{"Custom", "Value"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Attribute"),
    _1 = Table.ToColumns(#"Merged Columns"),
    _2 = Table.SelectRows(#"Promoted Headers", each ((try Number.From([Value]) otherwise -999) <>-999))[Value],
    colName = List.Combine({Table.ColumnNames(#"Merged Columns"),{"Value"}}),
    Custom1 = Table.FromColumns(_1&{_2},colName),
    #"Changed Type" = Table.TransformColumnTypes(Custom1,{{"Value", Int64.Type}})
in
    #"Changed Type"

ronrsnfld

Super User

4 years ago

Given your most recent set of data with four columns, you can modify my code to shift the Cost column by 2 to get your desired results.

let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Name", type text}, {"Value", type any}}),

//add index column which will retain rows in original order
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),

//custom pivot
    pivot = fnPivotAll(#"Added Index","Name","Value"),

//There will be some extra nulls as a result of above
//Each column will be offset by one (1) from the previous column
//So we readjust back
    offsetPrice = Table.FromColumns(
                    Table.ToColumns(Table.SelectColumns(pivot,{"ID","Category","Item"})) & 
                    {List.RemoveFirstN(pivot[Price],1) & {null}} &
                    {List.RemoveFirstN(pivot[Cost],2) & {null,null}},
                    type table[ID=Int64.Type, Category=text, Item=text,Price=Currency.Type,Cost=Currency.Type]),

//Now we can fill down the category column and filter out the nulls
    #"Filled Down" = Table.FillDown(offsetPrice,{"Category"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Item] <> null)),

//Merge the Category and Item columns for final report
    #"Merged Columns" = Table.CombineColumns(#"Filtered Rows",{"Category", "Item"},
        Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Category Item")
in
    #"Merged Columns"

However, if the number of columns will be dynamic --, then we should write a custom function which shifts the appropriate number of columns, the appropriate amount depending on how many "extra" columns there are.

Syndicate_Admin

Administrator

4 years ago

@ngct1112 you can transform it this way

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nRR0lHyS8xNBVJhiTmlqUqxOtFKhkCec2JJanp+USWQGZyRWZCbmlcCl/MsSc0FUo5wgYCizGSQEYYGBuiKnDAUGWEqcsapyAjVKS6pOZllqUAmTA7FKUbITjEFGhALAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ((try Number.From([Value]) otherwise -999) =-999)),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each if [Name]="Category" then [Value] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Filtered Rows1" = Table.SelectRows(#"Filled Down", each ([Name] = "Item")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Name"}),
    #"Merged Columns" = Table.CombineColumns(#"Removed Columns",{"Custom", "Value"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Attribute"),
    _1 = Table.ToColumns(#"Merged Columns"),
    _2 = Table.SelectRows(#"Promoted Headers", each ((try Number.From([Value]) otherwise -999) <>-999))[Value],
    colName = List.Combine({Table.ColumnNames(#"Merged Columns"),{"Value"}}),
    Custom1 = Table.FromColumns(_1&{_2},colName),
    #"Changed Type" = Table.TransformColumnTypes(Custom1,{{"Value", Int64.Type}})
in
    #"Changed Type"

ronrsnfld

Super User

4 years ago

Given:

The problem with a non-aggregated pivot in PQ is that it => errors where there are multiple entries for the same category.

This is handled nicely by a custom function developed by Cam Wallace, which I rename fnPivotAll to use in other queries

//credit: Cam Wallace  https://www.dingbatdata.com/2018/03/08/non-aggregate-pivot-with-multiple-rows-in-powerquery/

(Source as table,
    ColToPivot as text,
    ColForValues as text)=> 

let
     PivotColNames = List.Buffer(List.Distinct(Table.Column(Source,ColToPivot))),
     #"Pivoted Column" = Table.Pivot(Source, PivotColNames, ColToPivot, ColForValues, each _),
 
    TableFromRecordOfLists = (rec as record, fieldnames as list) =>
    
    let
        PartialRecord = Record.SelectFields(rec,fieldnames),
        RecordToList = Record.ToList(PartialRecord),
        Table = Table.FromColumns(RecordToList,fieldnames)
    in
        Table,
 
    #"Added Custom" = Table.AddColumn(#"Pivoted Column", "Values", each TableFromRecordOfLists(_,PivotColNames)),
    #"Removed Other Columns" = Table.RemoveColumns(#"Added Custom",PivotColNames),
    #"Expanded Values" = Table.ExpandTableColumn(#"Removed Other Columns", "Values", PivotColNames)
in
    #"Expanded Values"

So in your problem it would be used like:

let
    //data was pasted into a table
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJOLElNzy+qBDKDMzILclPzSpRidSByniWpuUDKES4QUJSZnAqkDQ0M0BU5YSgywlTkjFOREapTXFJzMstSgUyYHIpTjJCdYgo0IBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Name = _t, Value = _t]),
    
    //custom function to do a non-aggregated pivot
    pivotAll = fnPivotAll(Source,"Name","Value"),

    //fill down the Category column then ZMerge with the Item column
    #"Filled Down" = Table.FillDown(pivotAll,{"Category"}),
    #"Merged Columns" = Table.CombineColumns(#"Filled Down",{"Category", "Item"},
        Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Category Item"),
    
    //set the data type for the Price column
    typePrice = Table.TransformColumnTypes(#"Merged Columns",{"Price",Currency.Type})
in
    typePrice

Resulting in:

ngct1112
Post Patron
4 years ago
ronrsnfld , thanks but it is not working in my model.
I found out that the problem is my dataset is like that - there is no Price row for itemC
then the data is messed up after runnung the function.
IDNameValue
1 Category Shipment
1 Item A
1 Price 100
1 Item B
1 Price 200
1 Item C
2 Category Delivery
2 Item A
2 Price 150

Do you think it is possible to fix. Appreciated!
- ronrsnfld
  Super User
  4 years ago
  Your example with a missing `C Price` doesn't seem to show the problem.
  Before:
  
  After

wdx223_Daniel

Community Champion

4 years ago

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJOLElNzy+qBDKDMzILclPzSpRidSByniWpuUDKES4QUJSZnAqkDQ0M0BU5YSgywlTkjFOREapTXFJzMstSgUyYHIpTjJCdYgo0IBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Name = _t, Value = _t]),
    Custom1 = Table.ExpandTableColumn(Table.Group(Source,"ID",{"n",each Table.Combine(List.Transform(Table.Split(Table.Skip(_),2),(x)=>Table.AddColumn(Table.PromoteHeaders(Table.Skip(Table.Transpose(x))),[Name]{0},(x)=>[Value]{0})))}),"n",{"Category","Item","Price"})
in
    Custom1

ngct1112
Post Patron
4 years ago
wdx223_Daniel The script looks great, but it is not working in my dataset. In my dataset, like in the revised question, Item C may not have "Price".
Besdies, there are 22 distinct value in "Name".

How could I modify your code in this case. Great appredicated!
- wdx223_Daniel
  Community Champion
  4 years ago
  
  it seems my code is ok for your case.

Forum Discussion

Pivot Table with multiple same values

13 Replies

Related content

Pivot Unpivot Multiple values

How to pivot Multiple Columns

How to pivot with multiple values column?

DAX: Pivot table with multiple values.

Pivot multiple value

ID	Name	Value
1	Category	Shipment
1	Item	A
1	Price	100
1	Item	B
1	Price	200
1	Item	C
2	Category	Delivery
2	Item	A
2	Price	150