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.
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
Helpful resources
Power BI Monthly Update - November 2025
Check out the November 2025 Power BI update to learn about new features.
Fabric Data Days
Advance your Data & AI career with 50 days of live learning, contests, hands-on challenges, study groups & certifications and more!
FabCon Atlanta 2026
Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
