Pivot Columns turn Error

[Chanleakna123]

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 · Accepted solution]

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

[v-juanli-msft]

Hi @Chanleakna123

I work with a workaround, please try to see whether it helps you.

Add a index column in the table, then Pivot the table as i made before.

Then "Fill Up" for columns "manufcturing date", "expiring date", "row no", "pallets".

Remove empty for the column "productname"

Best regards

Maggie

[Chanleakna123]

I have 2 or 3 Rows , and 2 or 3 pallets as you can see above attached as per above attached first line i have 3 rows and 3 pallets with the product name 4223 Coke 330ml ,after fill up , i can see i lost the data and the data turns wrong , Sometimes this row Number => to the other pallets ,After i pivot , the row of each columns turn flip flop and the BI doesn't know which row for this pallets ,

[Chanleakna123]

hi @v-juanli-msft

i have below query , can you please help me to acheive this ? 

let
    Source = Csv.Document(Web.Contents("https://docs.google.com/spreadsheets/d/e/2PACX-1vRNfjJ7AeKkTQFtZ-7EMncdLLX2ZFHwAW6FiTNiOpUVuc_McUT_y...",", Columns=60, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Submission Date", type datetime}, {"FG Transfer", type text}, {"Date", type text}, {"Entry Your ID Below :", type text}, {"Assign Person to Confirm the Transfers :", type text}, {"Line", type text}, {"Shift", Int64.Type}, {"How many SKUs will you transfer to Warehouse today ?", type text}, {"Products Name :", type text}, {"Manufacturing Date :", type text}, {"Expiring Date :", type text}, {"Row No. :", type text}, {"Pallets :", Int64.Type}, {"Row No. : 2", type text}, {"Pallets : 2", Int64.Type}, {"Row No. : 3", type text}, {"Pallets : 3", Int64.Type}, {"Row No. : 4", type text}, {"Pallets : 4", Int64.Type}, {"QTY/Cases :", Int64.Type}, {"Total Pallets :", Int64.Type}, {"Total Cases/Pallet 1 :", Int64.Type}, {"Total Cases 1st SKU :", Int64.Type}, {"Products Name : 2", type text}, {"Manufacturing Date : 2", type text}, {"Expiring Date : 2", type text}, {"Row No. : 5", type text}, {"Pallets : 5", type text}, {"Row No. : 6", type text}, {"Pallets : 6", type text}, {"Row No. : 7", type text}, {"Pallets : 7", type text}, {"Row No. : 8", type text}, {"Pallets : 8", type text}, {"QTY/Cases : 2", type text}, {"Total Pallets : 2", type text}, {"Total Cases/Pallets 2 :", type text}, {"Total Cases 2nd SKU :", type text}, {"Products Name : 3", type text}, {"Manufacturing Date : 3", type text}, {"Expiring Date : 3", type text}, {"Row No. : 9", type text}, {"Pallets : 9", type text}, {"Row No. : 10", type text}, {"Pallets : 10", type text}, {"Row No. : 11", type text}, {"Pallets : 11", type text}, {"Row No. : 12", type text}, {"Pallets : 12", type text}, {"QTY/Cases : 3", type text}, {"Total Pallets : 3", type text}, {"Total Cases/Pallets 3 :", type text}, {"Total Cases 3rd SKU :", type text}, {"Total All Cases for these 3 SKUs :", Int64.Type}, {"Please Choose Below options for your acknowledgement to Confirm the Transfers:", type text}, {"Signature :", type text}, {"Get Page URL", type text}, {"IP", type text}, {"Submission ID", type number}, {"Edit Link", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Total Pallets :", "Total Cases/Pallet 1 :", "Total Cases 1st SKU :", "Total Pallets : 2", "Total Cases/Pallets 2 :", "Total Cases 2nd SKU :", "Signature :", "Get Page URL", "IP", "Submission ID", "Edit Link", "Total Cases 3rd SKU :", "Total All Cases for these 3 SKUs :"}),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Removed Columns", {"Products Name :", "Manufacturing Date :", "Expiring Date :", "Row No. :", "Pallets :", "Row No. : 2", "Pallets : 2", "Row No. : 3", "Pallets : 3", "Row No. : 4", "Pallets : 4", "QTY/Cases :", "Products Name : 2", "Manufacturing Date : 2", "Expiring Date : 2", "Row No. : 5", "Pallets : 5", "Row No. : 6", "Pallets : 6", "Row No. : 7", "Pallets : 7", "Row No. : 8", "Pallets : 8", "QTY/Cases : 2", "Products Name : 3", "Manufacturing Date : 3", "Expiring Date : 3", "Row No. : 9", "Pallets : 9", "Row No. : 10", "Pallets : 10", "Row No. : 11", "Pallets : 11", "Row No. : 12", "Pallets : 12", "QTY/Cases : 3", "Total Pallets : 3", "Total Cases/Pallets 3 :"}, "Attribute", "Value"),
    #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Only Selected Columns","Expiring Date : 2","Expiring Date",Replacer.ReplaceText,{"Attribute"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Expiring Date : ","Expiring Date",Replacer.ReplaceText,{"Attribute"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","Expiring Date3","Expirng Date :",Replacer.ReplaceText,{"Attribute"}),
    #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","Expiring Date : ","Expiring Date",Replacer.ReplaceText,{"Attribute"}),
    #"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3","Manufacturing Date : 2","Manufacturing Date : ",Replacer.ReplaceText,{"Attribute"}),
    #"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4","Manufacturing Date : 3","Manufacturing Date :",Replacer.ReplaceText,{"Attribute"}),
    #"Replaced Value6" = Table.ReplaceValue(#"Replaced Value5","Pallets : 10","Pallets :",Replacer.ReplaceText,{"Attribute"}),
    #"Replaced Value7" = Table.ReplaceValue(#"Replaced Value6","Pallets : 11","Pallets : ",Replacer.ReplaceText,{"Attribute"}),
    #"Replaced Value8" = Table.ReplaceValue(#"Replaced Value7","Pallets : 12","Pallets : ",Replacer.ReplaceText,{"Attribute"}),
    #"Replaced Value9" = Table.ReplaceValue(#"Replaced Value8","Pallets : 2","Pallets : ",Replacer.ReplaceText,{"Attribute"}),
    #"Replaced Value10" = Table.ReplaceValue(#"Replaced Value9","Pallets : 3","Pallets : ",Replacer.ReplaceText,{"Attribute"}),
    #"Replaced Value11" = Table.ReplaceValue(#"Replaced Value10","Pallets : 5","Pallets : ",Replacer.ReplaceText,{"Attribute"}),
    #"Replaced Value12" = Table.ReplaceValue(#"Replaced Value11","Pallets : 6","Pallets : ",Replacer.ReplaceText,{"Attribute"}),
    #"Replaced Value13" = Table.ReplaceValue(#"Replaced Value12","Pallets : 7","Pallets : ",Replacer.ReplaceText,{"Attribute"}),
    #"Replaced Value14" = Table.ReplaceValue(#"Replaced Value13","Pallets : 8","Pallets : ",Replacer.ReplaceText,{"Attribute"}),
    #"Replaced Value15" = Table.ReplaceValue(#"Replaced Value14","Pallets : 9","Pallets : ",Replacer.ReplaceText,{"Attribute"}),
    #"Replaced Value16" = Table.ReplaceValue(#"Replaced Value15","Row No. : 10","Row No. :",Replacer.ReplaceText,{"Attribute"}),
    #"Replaced Value17" = Table.ReplaceValue(#"Replaced Value16","Row No. : 5","Row No. : ",Replacer.ReplaceText,{"Attribute"}),
    #"Replaced Value18" = Table.ReplaceValue(#"Replaced Value17","Row No. : 12","Row No. : ",Replacer.ReplaceText,{"Attribute"}),
    #"Replaced Value19" = Table.ReplaceValue(#"Replaced Value18","Row No. : 2","Row No. : ",Replacer.ReplaceText,{"Attribute"}),
    #"Replaced Value20" = Table.ReplaceValue(#"Replaced Value19","Row No. : 3","Row No. : ",Replacer.ReplaceText,{"Attribute"}),
    #"Replaced Value21" = Table.ReplaceValue(#"Replaced Value20","Row No. : 4","Row No. : ",Replacer.ReplaceText,{"Attribute"}),
    #"Replaced Value22" = Table.ReplaceValue(#"Replaced Value21","Row No. : 11","Row No. : ",Replacer.ReplaceText,{"Attribute"}),
    #"Replaced Value23" = Table.ReplaceValue(#"Replaced Value22","Row No. : 6","Row No. : ",Replacer.ReplaceText,{"Attribute"}),
    #"Replaced Value24" = Table.ReplaceValue(#"Replaced Value23","Row No. : 7","Row No. : ",Replacer.ReplaceText,{"Attribute"}),
    #"Replaced Value25" = Table.ReplaceValue(#"Replaced Value24","Row No. : 8","Row No. : ",Replacer.ReplaceText,{"Attribute"}),
    #"Replaced Value26" = Table.ReplaceValue(#"Replaced Value25","Row No. : 9","Row No. : ",Replacer.ReplaceText,{"Attribute"}),
    #"Replaced Value27" = Table.ReplaceValue(#"Replaced Value26","Products Name : 2","Products Name : ",Replacer.ReplaceText,{"Attribute"}),
    #"Replaced Value28" = Table.ReplaceValue(#"Replaced Value27","Products Name : 3","Products Name : ",Replacer.ReplaceText,{"Attribute"}),
    #"Replaced Value29" = Table.ReplaceValue(#"Replaced Value28","QTY/Cases : 2","QTY/Cases :",Replacer.ReplaceText,{"Attribute"}),
    #"Replaced Value30" = Table.ReplaceValue(#"Replaced Value29","QTY/Cases : 3","QTY/Cases",Replacer.ReplaceText,{"Attribute"}),
    #"Replaced Value31" = Table.ReplaceValue(#"Replaced Value30","Expiring Date : ","Expiring Date :",Replacer.ReplaceText,{"Attribute"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value31", "Attribute", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type1",{"Attribute.2"}),
    #"Added Conditional Column" = Table.AddColumn(#"Removed Columns1", "Custom", each if [Attribute.1] = "Products Name " then [Value] else null),
    #"Added Index1" = Table.AddIndexColumn(#"Added Conditional Column", "Index", 1, 1),
    #"Filled Down" = Table.FillDown(#"Added Index1",{"Custom"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Custom] <> "")),
    #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[Attribute.1]), "Attribute.1", "Value"),
    #"Filled Up" = Table.FillUp(#"Pivoted Column",{"Manufacturing Date ", "Expiring Date ", "Row No. ", "Pallets "}),
    #"Filtered Rows1" = Table.SelectRows(#"Filled Up", each ([#"Products Name "] <> null))
in
    #"Filtered Rows1"

[v-juanli-msft]

Hi @Chanleakna123

Please download my pbix to see more details

Dataset before transform:

Dateset after transform

Best Regards

Maggie

[Chanleakna123]

Hi @v-juanli-msft I wish to have only Index , Product Name , Manufacturing Date , Expiring Date , Row No , Pallets Columns

the output you sent me is my current raw data looks look , but i'd like to combine these together in only one each row . 

Means 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.

and i could download the file but can't go to see the query , the source can't be seen. 

[v-juanli-msft · Accepted solution]

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

[Chanleakna123]

@v-juanli-msfthi , i can't view each steps on edit query , it shown like this , the source can't be detected. 

can you send me the file which i can see the each step how to get it done ? 
i'd like to see each of both files , thanks you for your support . hope to get your prompt response very soon. 

[v-juanli-msft]

Hi @Chanleakna123

How about clicking on other steps rather than the "source"?

My last two post provides the code in Advanced editor.

This the table i create in excel, you could paste it in your excel to create a new excel like mine, then replace the file path in my two codes with the file path in your PC.

Source = Excel.Workbook(File.Contents("C:\Users\maggiel\Desktop\case\12\12.25\12.25.xlsx"), null, true),

Replace the red part with your file path

a	value
productname	2736   dasani 500ml 4*6 pet kh
manufcturing date	10/22/2018
expiring date	10/22/2019
row no	ភាសាខ្មែរ
pallets	36
row no2	21
pallets2	46
row no3	23
pallets3	16
row no4	12
pallets4	18
manufcturing date	10/23/2018
expiring date	10/23/2019
row no	ភាសា
pallets	35
row no2	36
pallets2	37
row no3	38
pallets3	39
row no4	40
pallets4	41
productname	pet kh
manufcturing date	10/28/2018
expiring date	10/29/2019
row no	ភាសាខ្មែរ
pallets	11
row no2	12
pallets2	13
row no3	14
pallets3	15
row no4	16
pallets4	17

Best Regrads

Maggie

[Chanleakna123]

@v-juanli-msft , hi , 

thanks you so much for your support so far . the issue solved. i wanna click accept solution but it said it contains URL , blah blah , 

thanks you , once the BI Support team send me accept the solution i'll click accept. 

thaks you again 🙂 

[v-juanli-msft · Accepted solution]

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

[v-juanli-msft]

Hi @Chanleakna123

Please look into my test step by step

If mine has something different as yours, please let me know

To workaround this and solve the error, I replace the error with the text in Khmer Language

Best Regards

Maggie

[Chanleakna123]

@v-juanli-msft, thanks you for your response , 

But i have a lots of Different Rows . 
Means i have the data submitted everyday with different meaning in khmer. 

Your solution can only replace with 1 row 😞 

Can we have another solution ?