Forum Discussion

Jimmy801's avatar
Jimmy801
Community Champion
6 years ago

Hello shafeequem 

 

a general recomandation from my side. If you need to analyse a lot of data afterwards (not visuals), Excel is the tool to be used. And as Excel has also Power Query integrated (even an older version) you should easily be able to complete your task within Excel-PowerQuery.

A solution to your problem at high level could be like that

- create 3 different queries for your 3 data sources and bring them in a comparable format (think already here about your process -meaning, that choose a public folder and name your export files always the same)

- create a new query where you join all three data sources using the EmpNumber as key

- Expand every table and add new columns for every check you want to make (A good idea here is to use the prefix-function of the expandtablecolumn in order you can clearly identify from which data source your data is comming)

 

For sure there are more satisfactory solution possibile, but I think this is a very good starting point and its better to keep it easy, in case of possible changes.


If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

 

 

 

 

 

shafeequem's avatar
shafeequem
Regular Visitor
6 years ago

The data size is pretty huge. and we need to visualize the data for management. So we prefer PowerBI. Could someone please help me to get a good article or video for below steps

1. Join all three data sources 

2. prefix-function of the expandtablecolumn

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi shafeequem ,

     

    Thank you for your quesiton, it was an intersting problem to work on :).

    I think this is what you are trying to achive - sorry, this is quite a bit of coding, but, in theory, all you need is to change is the "external parameters".

     

    mSource is the list that keeps references to your tables (i.e. type table), not names of the tables (type text).

    mNumberOfColumnInIndex is the number of colums being evaluated. IMPORTANT! The code assumes that the fields/columns come in the same order in all columns! If not, please re-arrage it within the cource code of relevant tables. The total number of columes in each table does not matter unless it is more than or equal to mNumberOfColumnInIndex.

     

    Everything else is calcualted automatically.

    fBuildIndex adds a merge colums that combines values from the first mNumberOfColumnInIndex columns to serve as merge-on field.

    fFilterNotMatchingRows takes a table as a parameter (well technically a table index in the mSourceList) and iteratively goes pairs it with all tables in the mSource list to find descrepancies. This can be improved as there is no reason to run this type of merge on self, but I have not coded this exception. If the overall performance is terrible, this is one of the first things to look at.

     

    let
    //External parameters
    mSource = {HR, AD, #"Azure AD"},
    mNumberOfColumnInIndex = 5,

    //Calculated parameters
    mTableCount = List.Count(mSource),
    
    //Index Combine Funciton
    fBuildIndex = 
        (pTable)=>Table.AddColumn(pTable, "!MergeOn!", each Text.Combine(List.Accumulate(List.FirstN(Record.ToList(_),mNumberOfColumnInIndex), {}, (s, a)=> s & {Text.From(a)}), "<=&=>"), type text),

    PrepareTables = List.Accumulate(mSource, {}, (s,a)=> s & {fBuildIndex(a)}),

    fFilterNotMatchingRows = 
        (pIndex as number) => 
            List.Accumulate({0..mTableCount-1}, Table.FromList({}), 
                (s,a)=> 
                    let 
                        MergeAnti = Table.NestedJoin(PrepareTables{pIndex}, {"!MergeOn!"}, PrepareTables{a}, {"!MergeOn!"}, "!MergeFlag!", JoinKind.LeftAnti),
                        RemoveFields = Table.RemoveColumns(MergeAnti, {"!MergeOn!", "!MergeFlag!"}),
                        AddTableIndex = Table.AddColumn(RemoveFields, "!Difference!", each "Between table " & Number.ToText(pIndex) & " and table " & Number.ToText(a), type text),
                        AccumulatedTable = Table.Combine({s, AddTableIndex})
                    in AccumulatedTable
            ),
    
    ProcessTables = List.Accumulate({0..mTableCount-1}, Table.FromList({}), (s,a) => Table.Combine({s, fFilterNotMatchingRows(a)}))
   
in
    ProcessTables

     

     

    In this version it points to the table indexes that the difference relate to, it is possible to add functionality to point to an exect row number in the main table (on the left side of the LeftAnti merge). I did not implement it, but this is reasonbly simple addition.

     

    Kind regards,

    JB