Solved: Pivot Columns turn Error - Page 2

Chanleakna123 · ‎12-24-2018

HI , I tried pivot columns , and those turn error , i also use " Don't agrregate" type to pivot ,

i wanna pivot Attribute.1 with Value Column ,

Products Name , Manufacturing Date , Expiring Date , Row No. , Pallets ,

Row No. and Pallets turn all ERROR .

PS : Value Column also contain with Khmer Language , I'm not sure what happen here , can somebody help me in this regards ?

v-juanli-msft · ‎12-27-2018

Hi @Chanleakna123

Use unpivot columns, then all of the other Row No2 or No3 or No4 , will be all in Row No , and pallets also , All Pallets 2 or Pallets 3 or Pallets 4 only in Pallets Column.

But this will get many rows:

This is my code

let
    Source = Excel.Workbook(File.Contents("C:\Users\maggiel\Desktop\case\12\12.25\12.25.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"a", type text}, {"value", type any}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Pivoted Column" = Table.Pivot(#"Added Index", List.Distinct(#"Added Index"[a]), "a", "value"),
    #"Filled Up" = Table.FillUp(#"Pivoted Column",{"pallets4", "row no4", "row no2", "pallets2", "row no3", "pallets3", "pallets", "row no", "expiring date"}),
    #"Filled Down" = Table.FillDown(#"Filled Up",{"productname"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each [manufcturing date] <> null and [manufcturing date] <> ""),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", {"Index", "productname", "manufcturing date", "expiring date", "pallets", "pallets2", "pallets3", "pallets4"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Value", "row no"}}),
    #"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Index", "productname", "manufcturing date", "expiring date", "Attribute", "row no"}, "Attribute.1", "Value"),
    #"Renamed Columns1" = Table.RenameColumns(#"Unpivoted Columns1",{{"Value", "pallets"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns1",{"Attribute", "Attribute.1"})
in
    #"Removed Columns"

Best Regards

Maggie

View solution in original post

v-juanli-msft · ‎12-27-2018

Hi @Chanleakna123

If you want the "row no1" assciated with "pallets1", instead of "pallets2","pallets3",,ect.

Please refer to my pbix below

let
    Source = Excel.Workbook(File.Contents("C:\Users\maggiel\Desktop\case\12\12.25\12.25.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"a", type text}, {"value", type any}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Pivoted Column" = Table.Pivot(#"Added Index", List.Distinct(#"Added Index"[a]), "a", "value"),
    #"Filled Up" = Table.FillUp(#"Pivoted Column",{"pallets4", "row no4", "row no2", "pallets2", "row no3", "pallets3", "pallets", "row no", "expiring date"}),
    #"Filled Down" = Table.FillDown(#"Filled Up",{"productname"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each [manufcturing date] <> null and [manufcturing date] <> ""),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", {"Index", "productname", "manufcturing date", "expiring date", "pallets", "pallets2", "pallets3", "pallets4"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Value", "row no"}}),
    #"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Index", "productname", "manufcturing date", "expiring date", "Attribute", "row no"}, "Attribute.1", "Value"),
    #"Renamed Columns1" = Table.RenameColumns(#"Unpivoted Columns1",{{"Value", "pallets"}}),
    #"Inserted Last Characters" = Table.AddColumn(#"Renamed Columns1", "Last Characters_rowno", each Text.End([Attribute], 1), type text),
    #"Changed Type1" = Table.TransformColumnTypes(#"Inserted Last Characters",{{"Last Characters_rowno", Int64.Type}}),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type1", {{"Last Characters_rowno", 1}}),
    #"Inserted Last Characters1" = Table.AddColumn(#"Replaced Errors", "Last Characters_pallets", each Text.End([Attribute.1], 1), type text),
    #"Changed Type2" = Table.TransformColumnTypes(#"Inserted Last Characters1",{{"Last Characters_pallets", Int64.Type}}),
    #"Replaced Errors1" = Table.ReplaceErrorValues(#"Changed Type2", {{"Last Characters_pallets", 1}}),
    #"Added Conditional Column" = Table.AddColumn(#"Replaced Errors1", "compare", each if [Last Characters_rowno] = [Last Characters_pallets] then 1 else 0),
    #"Filtered Rows1" = Table.SelectRows(#"Added Conditional Column", each [compare] = 1)
in
    #"Filtered Rows1"

Explaination for "insert last characters">

Since your columns name look like this:

“Row No. :”,” Row No. : 2”..

I extract the last character of the column “Attribute”(get this by using Unpivot columns for several row no columns), then transform the type from “text” to “whole number”, then except the “Row No. :”, others will show numbers, next replace error with 1.

The same for pallets columns.

finally, create a condition column, then filter this column.

Best Regards

Maggie

View solution in original post