Forum Discussion
Resolver IIIPowery Query Distinct Count
Hello.
I was hoping somone knew how to do a Distinct list count in Power Query (inside the group by) and not Count nulls.
This should be 0 not 1
Hi Robert14358 ,
Please check the steps below.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrQ0UdJRMgBiEIrVwRCJBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PART_Hull_Name = _t, PART_SWBS = _t, PART_Total = _t, PART_Relaeased = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"PART_Hull_Name", Int64.Type}, {"PART_SWBS", Int64.Type}, {"PART_Total", type text}, {"PART_Relaeased", type text}}), #"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"PART_Total"}), #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","",null,Replacer.ReplaceValue,{"PART_Relaeased"}), #"Grouped Rows" = Table.Group(#"Replaced Value1", {"PART_Hull_Name", "PART_SWBS"}, {{"Data",each Table.FromColumns(List.Transform(List.Range(Table.ToColumns(_),2),(x)=> {List.Count(List.Distinct(x))}),{"PART_Total","PART_Relaeased"})}}), #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"PART_Total", "PART_Relaeased"}, {"PART_Total", "PART_Relaeased"}) in #"Expanded Data"Best Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
3 Replies
- amitchandak
Super User
Robert14358 , remove duplicate or group by
Remove Empty and Remove duplicate Rows (Power Query) : https://youtu.be/Hc5bIXkpGVE
https://docs.microsoft.com/en-us/power-query/group-by
If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. 
Robert14358i cannot remove rows like that becuase some will not have nulls in both columns, i only show that to show that is counting nulls,
Hi Robert14358 ,
Please check the steps below.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrQ0UdJRMgBiEIrVwRCJBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PART_Hull_Name = _t, PART_SWBS = _t, PART_Total = _t, PART_Relaeased = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"PART_Hull_Name", Int64.Type}, {"PART_SWBS", Int64.Type}, {"PART_Total", type text}, {"PART_Relaeased", type text}}), #"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"PART_Total"}), #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","",null,Replacer.ReplaceValue,{"PART_Relaeased"}), #"Grouped Rows" = Table.Group(#"Replaced Value1", {"PART_Hull_Name", "PART_SWBS"}, {{"Data",each Table.FromColumns(List.Transform(List.Range(Table.ToColumns(_),2),(x)=> {List.Count(List.Distinct(x))}),{"PART_Total","PART_Relaeased"})}}), #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"PART_Total", "PART_Relaeased"}, {"PART_Total", "PART_Relaeased"}) in #"Expanded Data"Best Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
