Forum Discussion

rajul_rstgi's avatar
rajul_rstgi
Frequent Visitor
8 years ago
Solved

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        A...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    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's avatar
rajul_rstgi
Frequent Visitor
8 years ago

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.

  • parry2k's avatar
    parry2k
    Super User
    8 years ago

    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's avatar
    stretcharm
    Memorable Member
    8 years ago

    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's avatar
      rajul_rstgi
      Frequent Visitor
      8 years ago

      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's avatar
        stretcharm
        Memorable Member
        8 years ago

        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"