Solved: Re: Trying to merge 27 queries into one final form... - Page 2

Rhothgar · ‎03-05-2025

Maybe I have set this up wrong and my structure is poor but I am only a beginner.

It feels so near yet so far.

With the much appreciated and fantastic help of @PwerQueryKees in creation of the OUTPUT code (which I have since messed with), I have come on leaps and bounds in understanding BUT this simply cannot be learned in a few hard days of trial and error.

In the following link to my spreadsheet, there are 27 queries beginning with the word "Extracted" and I need them to transform in the format shown on "OUTPUT REQUIRED" worksheet. I manually spent a lot of time yesterday preparing that worksheet to show what the sample data in "Slimmed down data" needs to transform into.

2025-03-04 WIP Sample Data Collation -Posted on Fabric Community - Copy.xlsx

At present, I have started to edit a query called "TEST TRANSFORM TO SAGE AUDIT TRAIL" which is my naive attempt to create the Sage format that you see in the attached screenshot below. Whilst they are no errors in that query, there are now some errors which have appeared in the OUTPUT query.

All tabs I mention above are located to the far left of the workbook.

Essentially, I now need to edit each and every one of those queries into the format shown on the output required tab and I have spent a good part of today trying to test various transforms. I am sure the solution when it is pointed out to me will be obvious but I simply cannot see it because I am getting confused.

In the below sample/example, only the Type JC / JD which I have manually input into the OUTPUT REQUIRED worksheet needs to have the date displayed as it is shown in the sample ie the base data. Each and every other query I have made End of Month the data entry date.

In the markup below, I have manually type the data into the Type, Account Reference, Nominal A/C Reference, Reference, Details, Tax Code and Tax Amount. For the Extra Reference column, I can copy, edit and merge data in the queries to formulate the written month and year as shown.

You will see in the TEST TRANSFORM query, I have added Columns from Examples in some cases and ended up creating Literals in others. I am unclear if this is the only way to produce data that doesn't exist in any other sheet.

The worksheet OUTPUT REQUIRED does have the correct headers that I need to transform all the other queries into.

MarkLaf · ‎03-06-2025

As mentioned, it's not really teneble to provide the whole solution, but here is an approach and some M that maybe will send you on the direction you are looking for.

The main idea is that we want to handle all your "extracted" calcs in one query by:

Do one big Group step that handles all your aggregations at once
Add literals per your logic
Unpivot these aggs to get something closer to your final desired output

For testing out the below, I just pasted your 'Slimmed down data' table from Excel into an "Enter Data" table in Power Query, which you can see is what I'm referencing in Source.

let
    Source = #"Slimmed down data",

    //Rename current Type col as we want to add a literal column called "Type" later on
    RenameCols = Table.RenameColumns(Source,{{"Type", "Order Type"}}),

    //Add col with the EO month except for payout
    AddEO = 
    Table.AddColumn(
        RenameCols, 
        "EOMonth", 
        each if [Order Type] = "Payout" 
            then [Transaction creation date] 
            else Date.EndOfMonth( [Transaction creation date] ), 
        type date 
    ),

    //Group by type and EO month. Handle all "extracted" logic and outputs here. 
    //If there are aggs that work across Type, then only group by EO Month 
    //and handle type filtering in the aggs
    GroupEOandType = 
    Table.Group(
        AddEO, 
        {"EOMonth", "Order Type"}, 
        {
            {
                "Payout Amount", 
                each if List.First( [Order Type] ) =  "Payout" 
                    then -List.Sum([Net amount])
                    else null , 
                type nullable number
            },
            {
                "Final Value Fees - Fixed", 
                each if List.First( [Order Type] ) =  "Payout" 
                    then null 
                    else -List.Sum([#"Final value fee – fixed"]), 
                type nullable number
            },
            {
                "International regulatory operating fee", 
                each if List.First( [Order Type] ) =  "Payout"
                    then null 
                    else List.Sum([Regulatory operating fee]), 
                type nullable number
            }
        }
    ),

    //Addding all literals in one step
    AddLiterals = 
    let 
        literalTableType = type table [Type = text, Account Reference = text, #"Nominal A/C Reference" = text ], 
        payoutLiteral = #table( literalTableType, { {"JC","1212",null},{"JD","1200",null} } ), 
        nonPayoutLiteral = #table( literalTableType, { {"BR","1212","4000"} } ) 
    in 
        Table.AddColumn(
            GroupEOandType, 
            "Literals", 
            each if [Order Type] = "Payout" 
                then payoutLiteral 
                else nonPayoutLiteral, 
            literalTableType 
        ),

    //Unpivot all the calcs we added with Group
    UnpivotCalcs = 
    Table.UnpivotOtherColumns(
        AddLiterals, 
        {"EOMonth", "Order Type", "Literals"}, 
        "Details", "Net Amount"
    ),

    ExpandLiterals = 
    Table.ExpandTableColumn(
        UnpivotCalcs, 
        "Literals", 
        {"Type", "Account Reference", "Nominal A/C Reference"}, 
        {"Type", "Account Reference", "Nominal A/C Reference"}
    )
in
    ExpandLiterals

Output:

In retrospect, it may make more sense to just handle Payout rows separately and put Payout and non-Payout together with Table.Combine at the end. Regardless, this perhaps gives you a framework to dabble with.

Edit: added output snip

View solution in original post

Rhothgar · ‎03-06-2025

I tagged him in the original post so I guess if he is up for it, he'll be in touch. He advised to do a separate post and keep it concise. I'm not very good at keeping things concise. I feel that I have to included all information to fully explain myself.

Probably not the best at summarising things.