Counting distinct values

[rajul_rstgi]

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

[rajul_rstgi]

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.

[stretcharm]

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?

[rajul_rstgi]

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.

[stretcharm]

Does this work for you?

let
    Query1 = #table(
 type table
    [
        #"SystemA_ID"=text, 
        #"SystemB_ID"=text, 
        #"SystemC_ID"=text, 
        #"System_A_Column1"=number, 
        #"System_B_Column1"=number, 
        #"System_C_Column1"=number
    ], 
{
        {"ID1",null,"ID1",1,null,1}
,        {"ID2","ID2",null,1,1,null}
,        {null,"ID3","ID3",null,1,1}
,        {"ID4","ID4",null,1,1,null}
,        {"ID5","ID5","ID5",1,1,1}
}),
    #"Changed Type" = Table.TransformColumnTypes(Query1,{{"SystemA_ID", type text}, {"SystemB_ID", type text}, {"SystemC_ID", type text}, {"System_A_Column1", Int64.Type}, {"System_B_Column1", Int64.Type}, {"System_C_Column1", Int64.Type}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "ID", each if [SystemA_ID] <> null then [SystemA_ID] else if [SystemB_ID] <> null then [SystemB_ID] else [SystemC_ID]),
    #"Added Custom" = Table.AddColumn(#"Added Conditional Column", "AMissingInB", each if [SystemA_ID] <> null and [SystemB_ID] = null then [SystemA_ID] else null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "AMissingInC", each if [SystemA_ID] <> null and [SystemC_ID] = null then [SystemA_ID] else null),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "BMissingInC", each if [SystemB_ID] <> null and [SystemC_ID] = null then [SystemB_ID] else null),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "BMissingInA", each if [SystemB_ID] <> null and [SystemA_ID] = null then [SystemB_ID] else null),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "CMissingInA", each if [SystemC_ID] <> null and [SystemA_ID] = null then [SystemC_ID] else null),
    #"Added Custom5" = Table.AddColumn(#"Added Custom4", "CMissingInB", each if [SystemC_ID] <> null and [SystemA_ID] = null then [SystemC_ID] else null),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Added Custom5", {"SystemA_ID", "SystemB_ID", "SystemC_ID", "System_A_Column1", "System_B_Column1", "System_C_Column1", "ID"}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Columns", {"Attribute"}, {{"Count", each Table.RowCount(Table.Distinct(_)), type number}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Grouped Rows", "Attribute", "Attribute - Copy"),
    #"Split Column by Position" = Table.SplitColumn(#"Duplicated Column", "Attribute - Copy", Splitter.SplitTextByPositions({0, 1}, false), {"Attribute - Copy.1", "Attribute - Copy.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Attribute - Copy.1", type text}, {"Attribute - Copy.2", type text}}),
    #"Split Column by Position1" = Table.SplitColumn(#"Changed Type1", "Attribute - Copy.2", Splitter.SplitTextByPositions({0, 1}, true), {"Attribute - Copy.2.1", "Attribute - Copy.2.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Position1",{{"Attribute - Copy.2.1", type text}, {"Attribute - Copy.2.2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"Attribute - Copy.2.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Attribute - Copy.1", "Source"}, {"Attribute - Copy.2.2", "Target"}})
in
    #"Renamed Columns"

Distinct IDs is a variation on the same code

let
    Query1 = #table(
 type table
    [
        #"SystemA_ID"=text, 
        #"SystemB_ID"=text, 
        #"SystemC_ID"=text, 
        #"System_A_Column1"=number, 
        #"System_B_Column1"=number, 
        #"System_C_Column1"=number
    ], 
{
        {"ID1",null,"ID1",1,null,1}
,        {"ID2","ID2",null,1,1,null}
,        {null,"ID3","ID3",null,1,1}
,        {"ID4","ID4",null,1,1,null}
,        {"ID5","ID5","ID5",1,1,1}
}),
    #"Changed Type" = Table.TransformColumnTypes(Query1,{{"SystemA_ID", type text}, {"SystemB_ID", type text}, {"SystemC_ID", type text}, {"System_A_Column1", Int64.Type}, {"System_B_Column1", Int64.Type}, {"System_C_Column1", Int64.Type}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "ID", each if [SystemA_ID] <> null then [SystemA_ID] else if [SystemB_ID] <> null then [SystemB_ID] else [SystemC_ID]),
    #"Added Custom" = Table.AddColumn(#"Added Conditional Column", "AMissingInB", each if [SystemA_ID] <> null and [SystemB_ID] = null then [SystemA_ID] else null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "AMissingInC", each if [SystemA_ID] <> null and [SystemC_ID] = null then [SystemA_ID] else null),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "BMissingInC", each if [SystemB_ID] <> null and [SystemC_ID] = null then [SystemB_ID] else null),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "BMissingInA", each if [SystemB_ID] <> null and [SystemA_ID] = null then [SystemB_ID] else null),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "CMissingInA", each if [SystemC_ID] <> null and [SystemA_ID] = null then [SystemC_ID] else null),
    #"Added Custom5" = Table.AddColumn(#"Added Custom4", "CMissingInB", each if [SystemC_ID] <> null and [SystemB_ID] = null then [SystemC_ID] else null),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Added Custom5", {"SystemA_ID", "SystemB_ID", "SystemC_ID", "System_A_Column1", "System_B_Column1", "System_C_Column1", "ID"}, "Attribute", "Value"),
    #"Removed Other Columns" = Table.SelectColumns(#"Unpivoted Columns",{"Value", "Attribute"}),
    #"Grouped Rows" = Table.Group(#"Removed Other Columns", {"Value"}, {{"Count", each Table.RowCount(Table.Distinct(_)), type number}})
in
    #"Grouped Rows"

You can also use the Left Anti Merge  (Rows only in the first Query) to get the same results using several queries.

Repeat this type of Query for each check

e..g. AMissinginB

let
    Source = Table.NestedJoin(SystemAIDs,{"ID"},SystemBIDs,{"ID"},"SystemBIDs",JoinKind.LeftAnti),
    #"Removed Columns" = Table.RemoveColumns(Source,{"SystemBIDs"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Attribute", each "AMissingInB")
in
    #"Added Custom"

Then Append and group

let
    Source = Table.Combine({AMissinginB, AMissinginC, BMissinginC, BMissinginA, CMissinginA, CMissinginB}),
    #"Grouped Rows" = Table.Group(Source, {"Attribute"}, {{"Count", each Table.RowCount(Table.Distinct(_)), type number}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Grouped Rows", "Attribute", "Attribute - Copy"),
    #"Split Column by Position" = Table.SplitColumn(#"Duplicated Column", "Attribute - Copy", Splitter.SplitTextByPositions({0, 1}, false), {"Attribute - Copy.1", "Attribute - Copy.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Attribute - Copy.1", type text}, {"Attribute - Copy.2", type text}}),
    #"Split Column by Position1" = Table.SplitColumn(#"Changed Type1", "Attribute - Copy.2", Splitter.SplitTextByPositions({0, 1}, true), {"Attribute - Copy.2.1", "Attribute - Copy.2.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Position1",{{"Attribute - Copy.2.1", type text}, {"Attribute - Copy.2.2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"Attribute - Copy.2.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Attribute - Copy.1", "Source"}, {"Attribute - Copy.2.2", "MissingFrom"}})
in
    #"Renamed Columns"

Distinct IDs is a different group

let
    Source = Table.Combine({AMissinginB, AMissinginC, BMissinginC, BMissinginA, CMissinginA, CMissinginB}),
    #"Grouped Rows" = Table.Group(Source, {"ID"}, {{"Count", each Table.RowCount(Table.Distinct(_)), type number}})
in
    #"Grouped Rows"

[Zubair_Muhammad]

HI @rajul_rstgi

 Except function in DAX Returns the rows of one table which do not appear in another table.

Try this MEASURE

ID's in B missing in C =
COUNTROWS (
    EXCEPT (
        FILTER ( ALL ( TableName[SystemB_ID] ), TableName[SystemB_ID] <> BLANK () ),
        ALL ( TableName[SystemC_ID] )
    )
)

[Zubair_Muhammad · Accepted solution]

@rajul_rstgi

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] )
)

[rajul_rstgi]

@Zubair_Muhammad and @stretcharm Thanks a lot for your help with thisl

[parry2k]

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

[stretcharm]

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.