Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
L70F
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
camargos88
Community Champion
Community Champion

@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
wdx223_Daniel
Super User
Super User

@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

 

Thanks a lot. Your solution work very well. 

//BR Larissa

camargos88
Community Champion
Community Champion

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

camargos88
Community Champion
Community Champion

@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!



Thanks a lot, Your solution works very well. 

BR//Larissa

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors