Forum Discussion
Counting distinct values
I am working with a dataset which is a output of full joins across 3-4 systems. I am trying to get distinct count of the id's missing in each system
System A System B System C
AAA AAA
BBB BBB
CCC CCC
DDD DDD
EEE EEE EEEE
CCC CCC
The output would be
Count of System A missing values = 1
Count of System B missing values = 1
Count of System B missing values = 2
Thanks
Above will return the Count of Missing IDs.
If you want a list of Missing IDs... Goto Modelling Tab and select NEW TABLE
List of ID's in B missing in C = EXCEPT ( FILTER ( ALL ( TableName[SystemB_ID] ), TableName[SystemB_ID] <> BLANK () ), ALL ( TableName[SystemC_ID] ) )
9 Replies
I would use the merge features of M (query editor) to get the data on a single row and then have an expression to count the missing values.
Load A B & C as 3 queries. Merge each system to each other on the Key with full outer and you should be able to get what you need.
Then use powerbi to display the counts and let you see the missing values. You can export data from a table or matrix which I find is helpful when I've found reconcillation issues.
Thanks. I already have the data merged in single row. I am trying to find a good way to create measure to do a distinct count on each of these columns missing values, based on the distinct values in other 2 columns.
Hope this helps clarify the problem statement.
here is how i will do,
- unpviot your raw table so you have all two column one system and 2nd id
- remove blank rows
create another table by dupliating your raw table after above steps are applied, this new table,
- remove system column
- remove duplciate rows
so basically this new table will have only unique ids
close query editore and create relationship betwee id from raw to this new unique table
create two measures in your raw table:
AllId = new table
Total Id = Count(AllId[Id])
Another measure,
Count = DISTINCTCOUNT(RawTable[Id])
Another measure,
Missing Ids = [Total Id] - [Count]
choose table visual, and drop following in table:
- System (Column from raw table)
- Missins Ids (calculated measure)
You will see missing id for each system, somethign like this
I've tried using append the system lists and pivot. I added the System name to each data set as a Source field.
let Source = Table.Combine({SystemA, SystemB, SystemC}), #"Grouped Rows" = Table.Group(Source, {"Source", "Key"}, {{"Count", each Table.RowCount(_), type number}}), #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Source]), "Source", "Count", List.Sum), #"Added Conditional Column" = Table.AddColumn(#"Pivoted Column", "SystemAMissing", each if [SystemA] = null then 1 else 0), #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "SystemBMissing", each if [SystemB] = null then 1 else 0), #"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column1", "SystemCMissing", each if [SystemC] = null then 1 else 0), #"Changed Type" = Table.TransformColumnTypes(#"Added Conditional Column2",{{"SystemAMissing", Int64.Type}, {"SystemBMissing", Int64.Type}, {"SystemCMissing", Int64.Type}}) in #"Changed Type"Does this do what you need?
Thanks a lot for your quick turnaround. Seems like I have not done a good job in stating the problem statement. Apologies. Here is is humble attempt at it. We currently have Power BI using a consolidated single view as a source. The view is constructed out of 3 underlying tables in database and uses full outer join to merge the data between the 3 systems. From PowerBI perspective we only have one source which would be the view with bunch of columns from the 3 systems.
We are trying to create a PowerBI report which would bring out the inconsistencies across the missing id's across the 3 tables and be able to represent them as counts and eventually be able to show what those inconsistencies are. I am trying to first get the counts going.
View Layout
Columns SystemA_ID SystemB_ID SystemC_ID System_A_Column1 System_B_column1 System_C_column1.. and so on
Values ID1 ID1 <value> <null> <value> ......
ID2 ID2 <value> <value> <null>
ID3 ID3 <null> <value> <value>
ID3 ID3 <null> <value> <value>
ID4 ID4 <value> <value> <null>
The intent here is to see how many of these distinct ID's are missing when compared against each other
ID's in A missing in B : 1
ID's in A missing in C : 2
ID's in B missing in C : 1
ID's in B missing in A : 1
and so on...
We would also want to pull in the total number of distinct ID's to graph the inconsistencies.
Thanks again for your help with this. Much appreciated.
