Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hi all,
please i need help,
i have a sales table, that has in "Stock" column duplicate values, i'm looking if there is a solution to delete duplicate values without deleting all the row.
info; duplicate it depends on the "Product ID" columns, not in "Stock" (because in Stock we can have duplicate values but different Product ID, in this case its Ok)
(i have the solution in excel formula, but i need it in power query)
thank you so much.
sample_file.xlsx
Solved! Go to Solution.
When tested using the Power BI tools, this code seems to execute much more rapidly than yours.
let
Source = Excel.CurrentWorkbook(){[Name="Table9"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer ID", type text}, {"Product ID", Int64.Type}, {"Sales", Int64.Type}, {"Stock", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Product ID"}, {
{"all", (t)=>
let
#"Add Shifted" = Table.FromColumns(
Table.ToColumns(t) &
{{null} & List.RemoveLastN(t[Stock],1)},
Table.ColumnNames(t) & {"Shifted"}),
#"Null Dup Stock" = Table.ReplaceValue(
#"Add Shifted",
each [Stock],
each [Shifted],
(x,y,z)=> if y = z then null else y,
{"Stock"}),
#"Remove Shifted" = Table.RemoveColumns(#"Null Dup Stock","Shifted")
in
#"Remove Shifted",
type table[Customer ID=text, Product ID=Int64.Type, Sales=Int64.Type, Stock=Int64.Type]}
}),
#"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Product ID"}),
#"Expanded all" = Table.ExpandTableColumn(#"Removed Columns", "all", Table.ColumnNames(#"Changed Type"))
in
#"Expanded all"
Results from your data
Hi @Sofinobi, different approach here.
Result
let
Source = Excel.Workbook(File.Contents("C:\Downloads\2024 Test duplicate.xlsx"), null, true),
Sales_Table = Source{[Item="Sales",Kind="Table"]}[Data],
GroupedRows = Table.Group(Sales_Table, {"Product ID"}, {{"All", each
[ a = Table.RemoveColumns(_, {"Stock"}),
b = Table.FromColumns(Table.ToColumns(a) & {{[Stock]{0}?} & List.Repeat({null}, Table.RowCount(a)-1)}, Value.Type(_))
][b], type table}}),
CombinedAll = Table.Combine(GroupedRows[All])
in
CombinedAll
Hi @Sofinobi, different approach here.
Result
let
Source = Excel.Workbook(File.Contents("C:\Downloads\2024 Test duplicate.xlsx"), null, true),
Sales_Table = Source{[Item="Sales",Kind="Table"]}[Data],
GroupedRows = Table.Group(Sales_Table, {"Product ID"}, {{"All", each
[ a = Table.RemoveColumns(_, {"Stock"}),
b = Table.FromColumns(Table.ToColumns(a) & {{[Stock]{0}?} & List.Repeat({null}, Table.RowCount(a)-1)}, Value.Type(_))
][b], type table}}),
CombinedAll = Table.Combine(GroupedRows[All])
in
CombinedAll
thank you @dufoq3 for your contribution, thats works perfectly, i'll test the speed with the fist code of @ronrsnfld
thank you so much my friend
Question: Why should the "Stock" value of 1,407 for Product ID 50159 be attributed to the Sales to Customer 3 instead of Customer 4 or 5?
Honestly, you should strive to break your model out into three different tables: Customer (dimension), Product (dimension) and Sales (fact).
That "Stock" column is an attribute of the Product dimesnion and has no business in the Sales fact table.
Proud to be a Super User! | |
i found a solution, but its really energivore. if there is a light version of this M code
#"Type modifié" = Table.TransformColumnTypes(Source,{ {"Product ID", Int64.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Type modifié", "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each List.PositionOf(#"Added Index"[Product ID],[Product ID])),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if [Index] = [Custom] then [Stock] else 0
)
in
#"Added Custom1"
When tested using the Power BI tools, this code seems to execute much more rapidly than yours.
let
Source = Excel.CurrentWorkbook(){[Name="Table9"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer ID", type text}, {"Product ID", Int64.Type}, {"Sales", Int64.Type}, {"Stock", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Product ID"}, {
{"all", (t)=>
let
#"Add Shifted" = Table.FromColumns(
Table.ToColumns(t) &
{{null} & List.RemoveLastN(t[Stock],1)},
Table.ColumnNames(t) & {"Shifted"}),
#"Null Dup Stock" = Table.ReplaceValue(
#"Add Shifted",
each [Stock],
each [Shifted],
(x,y,z)=> if y = z then null else y,
{"Stock"}),
#"Remove Shifted" = Table.RemoveColumns(#"Null Dup Stock","Shifted")
in
#"Remove Shifted",
type table[Customer ID=text, Product ID=Int64.Type, Sales=Int64.Type, Stock=Int64.Type]}
}),
#"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Product ID"}),
#"Expanded all" = Table.ExpandTableColumn(#"Removed Columns", "all", Table.ColumnNames(#"Changed Type"))
in
#"Expanded all"
Results from your data
thank you very much @ronrsnfld , your solution works perfectly, and so fast comparing to my first code.
thanks again.
hi @ToddChitt thank you for your answer,
the "Stock" value depends on the "Product ID", it doesn't matter if it is attributed to Costomer 3 or 4 or 5.
i agree with you about the model, (it's already a model with fact and Dim Tables), but for our business need, i have to send this "Table Situation" monthely.
Ah, OK, that is a different question. It is not about removing values from the SOURCE DATA, it is about removing values from a visualization. (Hint: this post probably belongs in the Desktop forum.)
I suggest you look into two DAX features:
Window Functions WINDOW function (DAX) - DAX | Microsoft Learn
and Visual Calculations Using visual calculations in Power BI Desktop - Power BI | Microsoft Learn
I would set up the table as you have shown with the four columns. Next, create a Visual Calculation (column) that looks at the previous (Hint: LAG) Product ID. Finally, create a second Visual Calculation that has IF/THEN logic like this: IF ([Procuct ID] <> [Previous Product Id], [Stock], "")
You could probably wrap the whole thing in one big DAX calculation statement but I like to break out the components during development and testing.
Hope that helps
Proud to be a Super User! | |
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
15 | |
13 | |
13 | |
12 | |
11 |