cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

Pivot Columns turn Error

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 ? 

 

1.PNG

2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support Team
Community Support Team

Re: Pivot Columns turn Error

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:

5.png

 

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

 

Community Support Team
Community Support Team

Re: Pivot Columns turn Error

Hi @Chanleakna123

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

4.png

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

12 REPLIES 12
Community Support Team
Community Support Team

Re: Pivot Columns turn Error

Hi @Chanleakna123

Please look into my test step by step

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

1.png

2.png

3.png

 

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

4.png

 

Best Regards

Maggie

 

Re: Pivot Columns turn Error

@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 Smiley Sad 

 

Can we have another solution ? 

Community Support Team
Community Support Team

Re: Pivot Columns turn Error

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.

5.png

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

6.png

Remove empty for the column "productname"

7.png

 

Best regards

Maggie

Re: Pivot Columns turn Error

1.PNGI 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 ,2.PNGafter fill up , i can see i lost the data and the data turns wrong , Sometimes this row Number => to the other pallets ,3.PNGAfter i pivot , the row of each columns turn flip flop and the BI doesn't know which row for this pallets ,

Re: Pivot Columns turn Error

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"

Community Support Team
Community Support Team

Re: Pivot Columns turn Error

Hi @Chanleakna123

Please download my pbix to see more details

Dataset before transform:

6.png

Dateset after transform

7.png

 

Best Regards

Maggie

Re: Pivot Columns turn Error

1.PNG

 

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. 

Community Support Team
Community Support Team

Re: Pivot Columns turn Error

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:

5.png

 

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

 

Community Support Team
Community Support Team

Re: Pivot Columns turn Error

Hi @Chanleakna123

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

4.png

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