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

Dynamic check of values on the table rows for duplicates and remove the second value

HI

I usually use Power query to format price lists to prepare them for script into ERP. Sometimes suppliers give the same prices for different quantities. This creates problems for further calculations when duplicates of values occur.

I couldn’t manage to find a good solution in PQ for this problem because it should be dynamically checked. I think VBA macro could be useful instead.

 I need help with the codes of solution.

 

The following check needs to be done:

On each row, the value in each cell needs to be compared with the value of the previous cell on the left which is not empty. If values are equal, the cell on the right must be blanked, otherwise, the value is retained.

 

Wished result: the table without duplicates values on the row for each item number .

Is it possible to get adjusted results in the same excel file instead of on a new sheet?

 

Thanks a lot for helping me.

Best regards

Larissa 

 

The example below on the picture following by a copy of the excel table.

 

The problem with duplicated prices.jpg

 

P/NPricePricePricePricePricePricePricePrice
Sum of Purchase priceColumn Labels       
Row Labels (Qty)1235810  
A5000       
B8900       
C8900 89007690    
D209       
E700       
F780445340265225225  
G930  520 410  
2 ACCEPTED SOLUTIONS

@L70F ,

 

I don't have Excel here, so I'm pasting the m code, you can paste it on the advanced editor on Excel (Power Query)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCtD3U9JRCijKTE4lm47ViVYKLs1VyE9TCCgtSs5ILE5VKICqcM7PKc3NU/BJTErNKQbyFXBikClB+eVQpQoagSWVmkAJQyA2AmJjIDYFYguQmAGKLkeQlIGBAUHjnUD6LYlQ6IyqEMo0N7PE1ApS7gJyo4ElQWNdQYYQYb0bSJ0FSJ2JCcjPxiYgtpEZiG1khCAROtyBLEtjZJNNjWA8E3BwgVBsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t, #"(blank).6" = _t, #"(blank).7" = _t, #"(blank).8" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}, {"(blank).4", type text}, {"(blank).5", type text}, {"(blank).6", type text}, {"(blank).7", type text}, {"(blank).8", type text}}),
    #"Removed Top Rows" = Table.Skip(#"Changed Type",3),
    #"Transposed Table" = Table.Transpose(#"Removed Top Rows"),
    #"Transposed Table1" = Table.Transpose(#"Transposed Table"),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Transposed Table1", {"Column1"}, "Attribute", "Value"),
    #"Trimmed Text" = Table.TransformColumns(#"Unpivoted Other Columns",{{"Value", Text.Trim, type text}}),
    #"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"Value", Text.Clean, type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Cleaned Text","",null,Replacer.ReplaceValue,{"Value"}),
    #"Added Index" = Table.AddIndexColumn(#"Replaced Value", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each let 
_product = [Column1],
_value = [Value],
_index = [Index]
 in 
if Table.RowCount(Table.SelectRows(#"Added Index", 
each [Column1] = _product
and [Index] < _index
and [Value] = _value)) > 0 then null else _value),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Value", "Index"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Attribute]), "Attribute", "Custom"),
    Result = Table.Skip(Table.DemoteHeaders(Table.FirstN(#"Changed Type", 3)), 1) & #"Pivoted Column"
in
  Result


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



View solution in original post

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Custom1 = #table(Table.ColumnNames(Source),Table.ToList(Source,each List.Transform(List.Positions(_),(x)=>if List.Contains(List.FirstN(_,x),_{x}) then null else _{x})))
in
    Custom1

wdx223_Daniel_0-1604276229390.png

 

View solution in original post

8 REPLIES 8
Super User II
Super User II

@L70F  this code maybe work

wdx223_Daniel_0-1604122030989.png

 

 Hi,

thank you for your reply. Could you send your code? In the picture, I see a little of the code string and it is different from the previous member solution. I would like to test both. 

Thanks and best regards

Larissa

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Custom1 = #table(Table.ColumnNames(Source),Table.ToList(Source,each List.Transform(List.Positions(_),(x)=>if List.Contains(List.FirstN(_,x),_{x}) then null else _{x})))
in
    Custom1

wdx223_Daniel_0-1604276229390.png

 

View solution in original post

Thanks a lot. Your solution work very well. 

//BR Larissa

Super User III
Super User III

Hi @L70F ,

 

Check if the attached file helps:

Capture.PNG



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Hi, thank you for your reply. Unfortunately, I can't open the attached file. Is it possible to get it as Excel?

Best regards

Larissa

@L70F ,

 

I don't have Excel here, so I'm pasting the m code, you can paste it on the advanced editor on Excel (Power Query)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCtD3U9JRCijKTE4lm47ViVYKLs1VyE9TCCgtSs5ILE5VKICqcM7PKc3NU/BJTErNKQbyFXBikClB+eVQpQoagSWVmkAJQyA2AmJjIDYFYguQmAGKLkeQlIGBAUHjnUD6LYlQ6IyqEMo0N7PE1ApS7gJyo4ElQWNdQYYQYb0bSJ0FSJ2JCcjPxiYgtpEZiG1khCAROtyBLEtjZJNNjWA8E3BwgVBsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t, #"(blank).6" = _t, #"(blank).7" = _t, #"(blank).8" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}, {"(blank).4", type text}, {"(blank).5", type text}, {"(blank).6", type text}, {"(blank).7", type text}, {"(blank).8", type text}}),
    #"Removed Top Rows" = Table.Skip(#"Changed Type",3),
    #"Transposed Table" = Table.Transpose(#"Removed Top Rows"),
    #"Transposed Table1" = Table.Transpose(#"Transposed Table"),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Transposed Table1", {"Column1"}, "Attribute", "Value"),
    #"Trimmed Text" = Table.TransformColumns(#"Unpivoted Other Columns",{{"Value", Text.Trim, type text}}),
    #"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"Value", Text.Clean, type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Cleaned Text","",null,Replacer.ReplaceValue,{"Value"}),
    #"Added Index" = Table.AddIndexColumn(#"Replaced Value", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each let 
_product = [Column1],
_value = [Value],
_index = [Index]
 in 
if Table.RowCount(Table.SelectRows(#"Added Index", 
each [Column1] = _product
and [Index] < _index
and [Value] = _value)) > 0 then null else _value),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Value", "Index"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Attribute]), "Attribute", "Custom"),
    Result = Table.Skip(Table.DemoteHeaders(Table.FirstN(#"Changed Type", 3)), 1) & #"Pivoted Column"
in
  Result


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



View solution in original post

Thanks a lot, Your solution works very well. 

BR//Larissa

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors