Reconciliation through Power Query

[hananak]

Dear All,

I am pulling data from 3 different sources using power query. I need to do reconciliation whether the hours worked are matching from these sources. Below are the headings of each source.

Table 1: A/C Code - Employee No - Month - Hours Worked

Table 2: A/C Code - Employee No - Month - Hours Worked

Table 3: A/C Code - Period - Total Hours Worked (No employee No) in source 3

The way I need to do the reconciliation is by A/C Code.

For Example:

The Final Outcome should be.

A/C Code - Employee No - Month - (Table 1 Hours) - (Table 2 Hours) - (Table 3 Hours)
9978 - Mr A - 1 - 20 - 25
9978 - Miss B - 1 - 30 - 30
Total for Month 1 - 50 - 55 - 70

Your expert advice would be appreciated

 

[AlienSx]

@hananak , with this code

let
    recon = (tbl) =>
        [pivot = Table.Pivot(tbl, List.Distinct(tbl[Name]), "Name", "Hours Worked"),
        rcn = pivot & 
            Table.FromRecords(
                    {[#"A/C Code" = null, 
                    Employee No = "Total for Month", 
                    Month = tbl{0}[Month],
                    Table1 = List.Sum(pivot[Table1]),
                    Table2 = List.Sum(pivot[Table2]),
                    Table3 = Table3{[#"A/C Code" = tbl{0}[#"A/C Code"], Period = Month]}[Total Hours Worked]]}
            )
        ][rcn],
    tables_12 = Record.ToTable(Record.SelectFields(#shared, {"Table1", "Table2"})),
    xp = Table.ExpandTableColumn(tables_12, "Value", {"A/C Code", "Employee No", "Month", "Hours Worked"}),
    group = Table.Group(xp, {"A/C Code", "Month"}, {{"x", recon}}),
    cmb = Table.Combine(group[x])
in
    cmb

you may get something like this:

but PQ is not the best tool to create such reports. PQ is best in data transformation for further processing in Power Pivot or Power BI (data model, measures, reports)

 

[hananak]

@hananak , with this code

let
    recon = (tbl) =>
        [pivot = Table.Pivot(tbl, List.Distinct(tbl[Name]), "Name", "Hours Worked"),
        rcn = pivot &
            Table.FromRecords(
                    {[#"A/C Code" = null,
                    Employee No = "Total for Month",
                    Month = tbl{0}[Month],
                    Table1 = List.Sum(pivot[Table1]),
                    Table2 = List.Sum(pivot[Table2]),
                    Table3 = Table3{[#"A/C Code" = tbl{0}[#"A/C Code"], Period = Month]}[Total Hours Worked]]}
            )
        ][rcn],
    tables_12 = Record.ToTable(Record.SelectFields(#shared, {"Table1", "Table2"})),
    xp = Table.ExpandTableColumn(tables_12, "Value", {"A/C Code", "Employee No", "Month", "Hours Worked"}),
    group = Table.Group(xp, {"A/C Code", "Month"}, {{"x", recon}}),
    cmb = Table.Combine(group[x])
in
    cmb

you may get something like this:
View attachment 112749
but PQ is not the best tool to create such reports. PQ is best in data transformation for further processing in Power Pivot or Power BI (data model, measures, reports)

Thanks for your reply.

I am not expert in PQ. Currently all the tables in my file are connected via Connection only.
At which level should I put this code?

I deleted all the current codes and pasted this code but it's not working.

Please help!

 

[AlienSx]

Currently all the tables in my file are connected via Connection only.

so do mine. Their names are Table1, Table2 and Table3. Create blank query and place my code in there.