Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
Robert14358
Resolver II
Resolver II

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

Robert14358_0-1676344599833.png
This should be 0 not 1

Robert14358_1-1676344617528.png

 

 

1 ACCEPTED SOLUTION
v-cgao-msft
Community Support
Community Support

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"

vcgaomsft_0-1677053434336.png

vcgaomsft_1-1677053455985.png

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

View solution in original post

3 REPLIES 3
v-cgao-msft
Community Support
Community Support

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"

vcgaomsft_0-1677053434336.png

vcgaomsft_1-1677053455985.png

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
Resolver II
Resolver II

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
Super User
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.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.