Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I'm hoping for a way to change duplicate rows to zero/null in Power Query/Dax . I have data which looks like the following:
I need to have a unique value rather than duplicate values in the Category Demand column, which would in turn be tied to my Index. That is, I would have one '40' value as for the Jan 23 - Bicycle index, and one '38' value as for the Feb 23 - Bicycle index. Any ideas?
Many thanks
Solved! Go to Solution.
see attached
https://1drv.ms/u/s!AiUZ0Ws7G26RhhxoO5MJGPHBG1zL?e=I6lIMB
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMUzAyVtBVcMpMrkzOSVXSgQoBGQih8IzU1BwgbWQAJEwMlGJ1iNUZnJpYAqQMSdbonJGYmQekTREa3VKT0DVChLC6FWylsQUJOqFuNSVVH8yphjCdsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, Date = _t, Category = _t, Type = _t, #"Type Demand" = _t, #"Category Demand" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", type text}, {"Date", type date}, {"Category", type text}, {"Type", type text}, {"Type Demand", Int64.Type}, {"Category Demand", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Index", "Date", "Category Demand"}, {{"Count", each Table.AddColumn(Table.AddIndexColumn(_, "Index_2", 1,1), "Category_Demand", each if [Index_2] > 1 then 0 else [Category Demand]), type table }}),
#"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"Count"}),
#"Expanded Count" = Table.ExpandTableColumn(#"Removed Other Columns", "Count", {"Index", "Date", "Category", "Type", "Type Demand", "Category_Demand"}, {"Index", "Date", "Category", "Type", "Type Demand", "Category_Demand"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Count",{{"Category_Demand", type number}})
in
#"Changed Type1"exColumn(_, "Index", 1,1), "Score_2", each if [Index] > 1 then null else [SCORE]), type table }})
Thank you Ahmed that looks very promising but I just have an issue applying it to my data. In the Power Query version, it is please possible if you could quickly provided a screenshot as to what you pressed and did to complete the Grouped Rows step, or were you simply using M code for this?
here in DAX we decided
https://1drv.ms/u/s!AiUZ0Ws7G26Rhh09Po3ZX3JF4Wo6?e=Mx7hpM
I also solved with Dax
Sure but I'm just encountering an issue using the DAX - bascially it isn't picking up the [Category Demand] in the final line of code. I've tried multiple attempts but still nothing. If it isn't much a bother, illustrating how you did it with Power Query would be really helpful and would also I feel be more useful to do the transformation here.
well watch the video and you will know how I could do it.
https://1drv.ms/v/s!AiUZ0Ws7G26RhiagoLqgNpMNzazK?e=go1gye
It works now, thanks a lot for the help.
see attached
https://1drv.ms/u/s!AiUZ0Ws7G26RhhxoO5MJGPHBG1zL?e=I6lIMB
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMUzAyVtBVcMpMrkzOSVXSgQoBGQih8IzU1BwgbWQAJEwMlGJ1iNUZnJpYAqQMSdbonJGYmQekTREa3VKT0DVChLC6FWylsQUJOqFuNSVVH8yphjCdsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, Date = _t, Category = _t, Type = _t, #"Type Demand" = _t, #"Category Demand" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", type text}, {"Date", type date}, {"Category", type text}, {"Type", type text}, {"Type Demand", Int64.Type}, {"Category Demand", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Index", "Date", "Category Demand"}, {{"Count", each Table.AddColumn(Table.AddIndexColumn(_, "Index_2", 1,1), "Category_Demand", each if [Index_2] > 1 then 0 else [Category Demand]), type table }}),
#"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"Count"}),
#"Expanded Count" = Table.ExpandTableColumn(#"Removed Other Columns", "Count", {"Index", "Date", "Category", "Type", "Type Demand", "Category_Demand"}, {"Index", "Date", "Category", "Type", "Type Demand", "Category_Demand"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Count",{{"Category_Demand", type number}})
in
#"Changed Type1"exColumn(_, "Index", 1,1), "Score_2", each if [Index] > 1 then null else [SCORE]), type table }})
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |