Stripe Platform Fees Allocation Engine

Data: folder ingestion & consolidation (reconstructed, generic path)

shared Data = let
    // ⚠️ UPDATE THIS PATH to point to your own "Data" folder
    Source = Folder.Files("...\Workfolder\Data"),

    // Ignore any hidden files in that folder
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),

    // Apply the "Transform File" custom function to each file's binary content
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),

    // Rename "Name" to "Source.Name" to avoid collision after expanding
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),

    // Keep only the file name and the transformed table column
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),

    // Expand the nested tables (produced by Excel.Workbook) into rows
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),

    // Set data types for the expanded metadata columns
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Name", type text}, {"Data", type any}, {"Item", type text}, {"Kind", type text}, {"Hidden", type logical}}),

    // Rename file/sheet identifier columns to friendlier names
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Source.Name", "Worksheet"}, {"Name", "TabName"}}),

    // Drop columns not needed going forward
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Item", "Kind", "Hidden"}),

    // Expand the actual sheet data (Column1..Column19) out into real columns
    #"Expanded Data" = Table.ExpandTableColumn(#"Removed Columns", "Data",
        {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19"},
        {"Data.Column1", "Data.Column2", "Data.Column3", "Data.Column4", "Data.Column5", "Data.Column6", "Data.Column7", "Data.Column8", "Data.Column9", "Data.Column10", "Data.Column11", "Data.Column12", "Data.Column13", "Data.Column14", "Data.Column15", "Data.Column16", "Data.Column17", "Data.Column18", "Data.Column19"}),

    // Promote the first row of data to become the column headers
    #"Promoted Headers" = Table.PromoteHeaders(#"Expanded Data", [PromoteAllScalars=true]),

    // Grab the (dynamic) names of the first two columns so we can rename them safely
    WorksheetColName = Table.ColumnNames(#"Promoted Headers"){0},
    TabNameColName = Table.ColumnNames(#"Promoted Headers"){1},

    // Rename those first two columns to consistent names
    #"Renamed Columns2" = Table.RenameColumns(#"Promoted Headers",{
        {TabNameColName, "TabName"},
        {WorksheetColName, "Worksheet"}
    }),

    // Remove any leftover header row that got mixed into the data (where Type = "Type")
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns2", each [Type] <> "Type"),

    // Set proper data types for date and currency fields
    #"Changed Type2" = Table.TransformColumnTypes(#"Filtered Rows",{{"Created", type date}, {"Fees", Currency.Type}}),

    // Build a "TabName + Year" label by pulling the 4-digit year out of the worksheet/file name
    #"Added Year to TabName" = Table.AddColumn(#"Changed Type2", "TabNameWithYear", each [TabName] & " " & Text.Middle([Worksheet], Text.PositionOf([Worksheet], "20"), 4), type text),

    // Drop the old TabName (without year) and rename the new one in its place
    #"Removed Old TabName" = Table.RemoveColumns(#"Added Year to TabName",{"TabName"}),
    #"Renamed Final" = Table.RenameColumns(#"Removed Old TabName",{{"TabNameWithYear", "TabName"}}),

    // Put columns in the final desired order
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Final",{"Worksheet", "TabName", "Type", "ID", "Created", "Description", "Amount", "Currency", "Converted Amount", "Fees", "Net", "Converted Currency", "Details", "Customer ID", "Customer Email", "rs_document (metadata)", "rs_component (metadata)", "rs_signer_name (metadata)", "rs_signer_email (metadata)", "card_last4 (metadata)", "Memo / Notes"}),

    // Final cleanup: drop any completely blank/null-type rows
    #"Filtered Rows1" = Table.SelectRows(#"Reordered Columns", each [Type] <> null)
in
    #"Filtered Rows1";

Transform Data for QB: filtered QuickBooks export

shared #"Transform Data for QB" = let
    // Pull the "Data" query's output from within this same workbook
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],

    // Set data types across all columns
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Fees-Date", type datetime}, {"Worksheet", type text}, {"TabName", type text}, {"Type", type text}, {"ID", type text}, {"Created", type datetime}, {"Description", type text}, {"Amount", type number}, {"Currency", type text}, {"Column1", type number}, {"Converted Amount", type number}, {"Fees", type number}, {"Net", type number}, {"Converted Currency", type text}, {"Details", type text}, {"Customer ID", type text}, {"Customer Email", type text}, {"rs_document (metadata)", type text}, {"rs_component (metadata)", type text}, {"rs_signer_name (metadata)", type any}, {"rs_signer_email (metadata)", type any}, {"card_last4 (metadata)", type any}, {"Memo / Notes", type text}}),

    // Keep only rows where the transaction Type is "Charge"
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Type] = "Charge")),

    // Remove metadata/columns not relevant for the QuickBooks export
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"rs_component (metadata)", "rs_signer_name (metadata)", "rs_signer_email (metadata)", "Converted Currency", "Column1", "Currency"}),

    // Simplify "Created" from datetime down to just date
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Created", type date}}),

    // Drop remaining columns not needed for the final QB export
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type1",{"Fees-Date", "card_last4 (metadata)", "Memo / Notes"})
in
    #"Removed Columns1";

Allocation logic (reconstructed, generic column names)

Allocation % = [Charge Amount] / SUMIFS([Charge Amount], [Charge Date], [@Charge Date])
Allocated Fee = [Allocation %] * XLOOKUP([@Charge Date], DailyFees[Date], DailyFees[Total Fee])