Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
P/N | Price | Price | Price | Price | Price | Price | Price | Price |
Sum of Purchase price | Column Labels | |||||||
Row Labels (Qty) | 1 | 2 | 3 | 5 | 8 | 10 | ||
A | 5000 | |||||||
B | 8900 | |||||||
C | 8900 | 8900 | 7690 | |||||
D | 209 | |||||||
E | 700 | |||||||
F | 780 | 445 | 340 | 265 | 225 | 225 | ||
G | 930 | 520 | 410 |
Solved! Go to Solution.
@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
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
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
Thanks a lot. Your solution work very well.
//BR Larissa
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
Thanks a lot, Your solution works very well.
BR//Larissa