Powery Query Distinct Count

[Robert14358]

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

[v-cgao-msft · Accepted solution]

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 Team

If 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

[Robert14358]

i cannot remove rows like that becuase some will not have nulls in both columns, i only show that to show that is counting nulls,

[amitchandak]

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