Trying to merge 27 queries into one final format worksheet

[Rhothgar]

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.

[Rhothgar]

Hi @MarkLaf 

Hope you had a great weekend.

I spent yesterday and today on this and I haven't got very far and now stuck again.

Here is my latest code:-

let
    Source = Table.Combine({ Aggs_Payout, Aggs_NonPayout } ), //<---- Table.Combine use

    AddLiterals =
    let
        literalTableType = 
        type table [
            Type = text, 
            Account Reference = text, 
            #"Nominal A/C Reference" = text, 
            Department = text,
            Reference = text,
            Tax Code = text,
            Tax Amount = text,
            Exchange = text,
            Extra Reference = text
        ],
        payoutLiteral = #table(literalTableType, 
            { 
                {"JC", "1212", null, null, null, "T9", "0.00", null, null}, 
                {"JD", "1200", null, null, null, "T9", "0.00", null, null} 
            } 
        ),
        nonPayoutLiteral = #table(literalTableType, 
            { 
                {"BP", "1212", "4000", null, null, "T9", "0.00", null, null} 
            } 
        ),
        orderPaymentLiteral = #table(literalTableType, 
            { 
                {"BR", "1212", "4000", null, null, "T9", "0.00", null, null} 
            } 
        ),
        refundPaymentLiteral = #table(literalTableType, 
            { 
                {"BP", "1212", "4000", null, null, "T9", "0.00", null, null} 
            } 
        ),
        otherFeePaymentLiteral = #table(literalTableType, 
            { 
                {"BP", "1212", "6000", null, null, "T9", "0.00", null, null} 
            } 
        ),
        postageLabelLiteral = #table(literalTableType, 
            { 
                {"BP", "1212", "6000", null, null, "T9", "0.00", null, null} 
            } 
        )
    in
        Table.AddColumn(
            Source,
            "Literals",
            each if [Order Type] = "Order"
                then orderPaymentLiteral
                else if [Order Type] = "Refund"
                    then refundPaymentLiteral
                else if [Order Type] = "Other fee"
                    then otherFeePaymentLiteral
                else if [Order Type] = "Postage label"
                    then postageLabelLiteral
                else if [Order Type] = "Payout"
                    then payoutLiteral
                    else nonPayoutLiteral, //if type <> order AND type <> payout
            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", "Department", "Reference", "Tax Code", "Tax Amount", "Exchange", "Extra Reference"}, 
        {"Type", "Account Reference", "Nominal A/C Reference", "Department", "Reference", "Tax Code", "Tax Amount", "Exchange", "Extra Reference"}
    )
in
    ExpandLiterals

 I probably need to review what I am trying to achieve now as I think I am confused from looking at it.

I know I am very close to what I want to see.  I may even been there.  My thoughts are a bit mush at the moment.

I think where the "Order Type" is Refund and the "Details" are gross transaction amount then I need to put a BP literal but where the "Order Type" is Refund and the "Details" are anything other than gross transaction amount then I need a BR.  I'll give it some more thought to see if I can crack it.

The following code doesn't work but I was thinking it might be something like that:-

    in
        Table.AddColumn(
            Source,
            "Literals",
            each if [Order Type] = "Order"
                then orderPaymentLiteral
                else if [Order Type] = "Refund" and [Details] = "Gross transaction amount"
                    then refundPaymentLiteral
                else if [Order Type] = "Refund" and [Details] <> "Gross transaction amount"
                    then refundReceiptLiteral
                else if [Order Type] = "Other fee"
                    then otherFeePaymentLiteral
                else if [Order Type] = "Postage label"
                    then postageLabelLiteral
                else if [Order Type] = "Payout"
                    then payoutLiteral
                    else nonPayoutLiteral, //if type <> order AND type <> payout
            literalTableType
        ),

Is there, I don't know what the proper term is, an operator or a way to check what is in the Order Type and also what is in the Details column and return a different literal according my needs ie a BP if Refund + Gross transaction amount or a BR if Refund + anything other than the gross transaction amount.  I don't mind typing things out longhand as it might help me to understand the coding better.

Or maybe I just split out "Order Type" Order, Refund, Other fee, Postage labels, etceteras into different aggs?

[Rhothgar]

Also tried adding in more Literals but am tripping up here as well:-

Do I need to create a separate AddEO and Type step for Orders, Refunds, etceteras and then separate Groups and Literals to amalgamate the separate types of "Order Type" data I wonder?

[MarkLaf]

For future, please paste your code in as text rather than an image so it's easier to copy/modify.

The literals part looks nearly there, the problem is the structure of your if/then/else statement inside the function argument (third argument) of Table.AddColumn. There needs to only be one "each" and the if/then/else of payouts needs to be merged with if/then/else of orders.

Given that your nonOrderLiteral and orderLiteral are identical, I think perhaps this is what you are looking for:

// Adding all literals in one step
AddLiterals =
let
    literalTableType = 
    type table [
        Type = text, 
        Account Reference = text, 
        #"Nominal A/C Reference" = text, 
        Tax Amount = text, 
        Extra Reference = text
    ],
    payoutLiteral = #table(literalTableType, 
        { 
            {"P", "1212", null, "0.00", null}, 
            {"OD", "1200", null, "0.00", null} 
        } 
    ),
    nonPayoutLiteral = #table(literalTableType, 
        { 
            {"BP", "1212", "4000", "0.00", null} 
        } 
    ),
    orderLiteral = #table(literalTableType, 
        { 
            {"BP", "1212", "6000", "0.00", null} 
        } 
    )
in
    Table.AddColumn(
        GroupEndType,
        "Literals",
        each if [Order Type] = "Order"
            then orderLiteral
            else if [Order Type] = "Payout"
                then payoutLiteral
                else nonPayoutLiteral, //if type <> order AND type <> payout
        literalTableType
    ),

Edit: Added some formatting to code for readability

[Rhothgar]

Thanks for all your encouragement and help so far, @MarkLaf  and also @PwerQueryKees too from another post.

@MarkLaf One issue I do have which I haven't got onto yet. Is that all Net Amounts must be a positive decimal number.  Whilst sorting Order Types to "Order", some columns will be a BP and some will be a BR. 

There is where I am now with the code.

I think I am getting the hang of it - SLOWLY!

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

    //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 fee - 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
            },
            {
                "Final value fee - variable", 
                each if List.First( [Order Type] ) =  "Payout" 
                    then null 
                    else -List.Sum([#"Final value fee – variable"]), 
                type nullable number
            },
            {
                "Very high 'item not as described' fee", 
                each if List.First( [Order Type] ) =  "Payout" 
                    then null 
                    else -List.Sum([#"Very high 'item not as described' 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, Tax Amount = text, Extra Reference = text], 
        payoutLiteral = #table( literalTableType, { {"JC","1212",null, "0.00", null},{"JD","1200",null, "0.00", null} } ), 
        nonPayoutLiteral = #table( literalTableType, { {"BR","1212","4000", "0.00", null} } )
    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"
    ),
    #"Expanded Literals" = Table.ExpandTableColumn(UnpivotCalcs, "Literals", {"Type", "Account Reference", "Nominal A/C Reference", "Tax Amount", "Extra Reference"}, {"Type", "Account Reference", "Nominal A/C Reference", "Tax Amount", "Extra Reference"})
in
    #"Expanded Literals"

[MarkLaf]

Hopefully, my other post on the literals helped with "Whilst sorting Order Types to "Order", some columns will be a BP and some will be a BR." Otherwise, try to articulate in pseudo code what the logic for BR vs BP assignment, then convert into M. You can probably use the structure we've set up for Table.AddColumn in AddLiterals step.

If your net amount needs to always be positive, look into utilizing Number.Abs. E.g.

<...>
        {
            "Final value fee - fixed", 
            each if List.First( [Order Type] ) =  "Payout" 
                then null 
                else Number.Abs( List.Sum([#"Final value fee - fixed"]) ), 
            type nullable number
        },
<...>

Edit: typo

[Rhothgar]

Also tried Table Combine to no avail.

This is more complex than I first realised.

Just can't believe I managed to get so far only to fall flat on my face.  I reckon that I've structured it all wrong from the outset.  Just been reading about dynamic column headings so may start from scratch again as none of the column headings seem to be being picked up when I add them to the "Group by Type" step.

I have, however, got rid of the errors.  The columns in the Slimmed Down Data were set to any instead of decimal numbers so once I converted them it resolved.

This means I can now take a look at the grouping and try and get that working.

[MarkLaf]

I'll clarify my Table.Combine comment from before.

In the approah I provided, you can see that for each aggregation in the Table.Group step, we had logic to treat Type=Payout different from Type<>Payout.

A different approach would be to split your query into two, one where your pre-grouped table is filtered to Type=Payout and another where your pre-grouped table is filtered to Type<>Payout. Do the grouping and aggregations on these two separate queries. Then you can combine them and do any final transformations like adding unpvitoing and literals, etc.

So, the overall flow of the queries would go from:

To this:

Although this looks more complicated, it simplifies the logic you need to worry about at the aggregation step, which is where you are doing a lot of work so perhaps is worthwhile. Also, you can probably merge the small amount of transforms in the new Slimmed down data_Refined query into Slimmed down data - I don't see too much value in having those be a separate query step, but included in that way to account for all the M from the previous approach.

The queries would look like this:

Slimmed down data_Refined (as mentioned, probably you could just move these steps into Slimmed down data rather than give them their own query)

let
    Source = #"Slimmed down data",

    //Rename this as we want to add a 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 
    )
in
    AddEO

Aggs_Payout (you can see that we can forget about the checking whether something is Type=Payout in each aggregation, since we do that filter before the group

let
    Source = #"Slimmed down data_Refined",
    FilterInPayout = Table.SelectRows(Source, each ([Order Type] = "Payout")),

    //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(
        FilterInPayout, 
        {"EOMonth", "Order Type"}, 
        {
            {
                "Payout Amount", 
                each Number.Abs( List.Sum([Net amount]) ), 
                type nullable number
            }
        }
    )
in
    GroupEOandType

Aggs_NonPayout

let
    Source = #"Slimmed down data_Refined",
    FilterOutPayout = Table.SelectRows(Source, each ([Order Type] <> "Payout")),

    //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(
        FilterOutPayout, 
        {"EOMonth", "Order Type"}, 
        {
            {
                "Final value fee - fixed", 
                each Number.Abs( List.Sum([#"Final value fee – fixed"]) ), 
                type nullable number
            },
            {
                "International regulatory operating fee", 
                each Number.Abs(  List.Sum([Regulatory operating fee]) ), 
                type nullable number
            },
            {
                "Final value fee - variable", 
                each Number.Abs( List.Sum([#"Final value fee – variable"]) ), 
                type nullable number
            },
            {
                "Very high 'item not as described' fee", 
                each Number.Abs( List.Sum([#"Very high 'item not as described' fee"]) ), 
                type nullable number
            }
        }
    )
in
    GroupEOandType

Output

let
    Source = Table.Combine({ Aggs_Payout, Aggs_NonPayout } ), //<---- Table.Combine use

    AddLiterals =
    let
        literalTableType = 
        type table [
            Type = text, 
            Account Reference = text, 
            #"Nominal A/C Reference" = text, 
            Tax Amount = text, 
            Extra Reference = text
        ],
        payoutLiteral = #table(literalTableType, 
            { 
                {"P", "1212", null, "0.00", null}, 
                {"OD", "1200", null, "0.00", null} 
            } 
        ),
        nonPayoutLiteral = #table(literalTableType, 
            { 
                {"BP", "1212", "4000", "0.00", null} 
            } 
        ),
        orderLiteral = #table(literalTableType, 
            { 
                {"BP", "1212", "6000", "0.00", null} 
            } 
        )
    in
        Table.AddColumn(
            Source,
            "Literals",
            each if [Order Type] = "Order"
                then orderLiteral
                else if [Order Type] = "Payout"
                    then payoutLiteral
                    else nonPayoutLiteral, //if type <> order AND type <> payout
            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

Note that you only need to load the final query, the interim queries can be set to Connection Only in Excel.

[Rhothgar]

Afternoon @MarkLaf 

Hope you had a good weekend.

I spent yesterday playing around with this and also today and I've not got very far at all at present.

Can you explain the highlighted subtlety (ie the # symbol) I noticed in your last code and what it does please?


In the meantime, I will continue playing.

I was going to post some code earlier but thought I would keep playing instead to see if I can get it closer to what I need.

[MarkLaf]

That's just the syntax for wrapping references with special characters. For example, in the sub step where we are defining table schema, literalTableType, the below would also be valid.

literalTableType = 
    type table [
        #"Type" = text, 
        #"Account Reference" = text, 
        #"Nominal A/C Reference" = text, 
        #"Tax Amount" = text, 
        #"Extra Reference" = text
    ]

You will see the same wrapper syntax used when referencing queries and query steps. Examples:

//Paste this into advanced editor and you have no probelms evaluating
let
    SomeNumber = 0,

    AddToSomeNumber = SomeNumber + 1

in
    AddToSomeNumber


//Add spaces to step names and now you will get errors when PQ tries to evaluate
let
    Some Number = 0,

    Add To Some Number = Some Number + 1

in
    Add To Some Number


//This is what #"" syntax is for. The below evaluates fine
let
    #"Some Number" = 0,

    #"Add To Some Number" = #"Some Number" + 1

in
    #"Add To Some Number"

You can see that what counts as a special character that needs to be referenced wtih #"" will sometimes depend. With queries/steps, a space counts and thus #"" is required for references. Not so for record field names (which table type definition follows).

[v-karpurapud]

Hello @Rhothgar 

The solution provided by @MarkLaf is accurate and addresses your issue. Could you please confirm if your query has been resolved? If it has, kindly mark the helpful response and accept it as the solution. This will assist other community members in resolving similar issues more efficiently.

Thank you

[MarkLaf · Accepted solution]

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:

1. Do one big Group step that handles all your aggregations at once
2. Add literals per your logic
3. 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

[Rhothgar]

@MarkLaf 

I'm not sure what I am doing wrong.  I spent a couple of hours yesterday trying to get the same output as you but it shows errors.

I'm obviously missing something.  I'll give it another go now as my concentration levels yesterday were not too good.

Do these screenshots give any indication to you of what I might be doing wrong please?

I am trying to paste into a new blank workbook.  Should I be copying the data into a new query within the same workbook?  Or perhaps even copying Slimmed down data in a new workbook and then importing that back into the same workbook?

I also have errors in the same workbook (first photo) when copying and pasting your code.  I know I am doing something wrong but cannot work out what it is.

[Rhothgar]

I will need to spend some time looking at this over the coming days or perhaps when I am on holiday next week (I get bored if my mind isn't doing something worthwhile!).

It looks great and it's good to be able to learn from all the experts on here.

I wasn't sure but I did think that having Type in more than one location might cause something to break.

I guess the secret for a beginner is to copy queries, make changes and test what does or does not work until it all starts to become clearer.

Thanks for your input.

[v-karpurapud]

Hi @Rhothgar 

Thank you for you reply. Looking forward to your update!

[PwerQueryKees]

This is too much for me to digest, I also have a daytime job 😀 

I do get the feeling you are making it way too complicated for yourself with all these separate queries. Maybe you should consider to contract an expert to help you set it up from scratch...

[Rhothgar]

I think you could be right. I actually probably don't really understand the basics beyond clicking here and there.

I need to break down what you did and fully understand each step together with other advice.  It's good learning curve.

Thanks for all your help.

[ABD128]

Hello @Rhothgar 

Since there was a huge data and 27 queries. It is more complex to work on it.Looking the data itself is messy.  I think you would ask @PwerQueryKees to help you on this. Hope he will have better idea on this.

[Rhothgar]

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.

[ABD128]

Hello @Rhothgar 

Need the experts of power query to work on it. It's better to ask @PwerQuerykees help .