PowerQuery: get data from two sources (of the same dimensions)

[deLockloire]

Hi,

I need to create a table from a bank statement. The statement can be downloaded in XML or XLS format. However, neither format is perfect. Only the XML contains the dates properly, and only the XLS contains transactions properly. I did a power query for the XLS, but I would like to get the column for the dates from the XML. Can it be done in one query (without having to create a query for each format and then creating a third query for merging them)?

Thanks

 

[RoryA]

You don't need separate queries - the query for each can be steps within one main query. I don't really see that it makes any difference though?

 

[deLockloire]

The reason is that then there is only one query to maintain later on. However, I'm not sure how to do it, as even though they are the same statement in different file formats, there is no column in which values are unique in each row and are the same between both files. I tried the transaction amount, but there are a lot of cases where the transaction amount is the same, and power query simply mixes up the dates. Now the transactions are in the same order in both files. So theoretically, I could just create two queries and copy-paste the date column from the .xml to the .xls query and call it a day, but it would be nicer to automate it. Any pointers?

Also, I have never done queries from multiple sources, so not idea how it should be done. But basically I only need one column from the xml file to replace the date column from the xls query.

 

[RoryA]

You could add an index column to both, but it might be better to try and fix the issues with on or other file in PQ. What is the specific problem with the dates for example?

 

[deLockloire]

It only contains dates. The XML contains date/time/timezones. Unfortunately, it is not fixable. And the XML is missing amounts from the original currency of the transaction. So going one file only is not viable. So I would you suggest to add an index column? And what would it contain? Incrementally increasing numbers? Anyway, whatever your suggestion is, could you walk me through the specifics of how to implement it? Thank you!

 

[RoryA]

Load both files into power query as separate queries (we can always reduce this later), then on the Add column tab choose Index column (repeat with each query). You should then be able to join the two queries on the index columns and verify the output looks correct.

 

[deLockloire]

Okay. It's done, and it looks correct.

 

[deLockloire]

Now how do I replace the three queries I now have to just one?

 

[RoryA]

Post the M code of the three and I'm sure we can combine them.

 

[deLockloire]

XML file:

let
    Source = Xml.Tables(File.Contents("C:\Users\lacik\OneDrive\Desktop\CURRENT\002 - Template Files\WISE-EUR-statement.xml")),
    Table0 = Source{0}[Table],
    Table1 = Table0{1}[Table],
    #"Expanded Acct" = Table.ExpandTableColumn(Table1, "Acct", {"Id", "Ccy", "Ownr", "Svcr"}, {"Acct.Id", "Acct.Ccy", "Acct.Ownr", "Acct.Svcr"}),
    #"Expanded Acct.Id" = Table.ExpandTableColumn(#"Expanded Acct", "Acct.Id", {"IBAN"}, {"IBAN"}),
    #"Expanded Ntry" = Table.ExpandTableColumn(#"Expanded Acct.Id", "Ntry", {"NtryRef", "Amt", "CdtDbtInd", "Sts", "BookgDt", "BkTxCd", "NtryDtls", "AmtDtls"}, {"Ntry.NtryRef", "Ntry.Amt", "Ntry.CdtDbtInd", "Ntry.Sts", "Ntry.BookgDt", "Ntry.BkTxCd", "Ntry.NtryDtls", "Ntry.AmtDtls"}),
    #"Expanded Ntry.Amt" = Table.ExpandTableColumn(#"Expanded Ntry", "Ntry.Amt", {"Element:Text", "Attribute:Ccy"}, {"Ntry.Amt.Element:Text", "Ntry.Amt.Attribute:Ccy"}),
    #"Expanded Ntry.BookgDt" = Table.ExpandTableColumn(#"Expanded Ntry.Amt", "Ntry.BookgDt", {"DtTm"}, {"Date"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Ntry.BookgDt",{"IBAN", "Ntry.Amt.Element:Text", "Ntry.CdtDbtInd", "Date"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Other Columns",".",",",Replacer.ReplaceText,{"Ntry.Amt.Element:Text"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Ntry.Amt.Element:Text", type number}, {"Date", type datetimezone}}),
    #"Reversed Rows" = Table.ReverseRows(#"Changed Type"),
    #"Added Index" = Table.AddIndexColumn(#"Reversed Rows", "Index", 1, 1, Int64.Type),
    #"Renamed Columns" = Table.RenameColumns(#"Added Index",{{"Date", "DateTime"}, {"Ntry.Amt.Element:Text", "Amount"}, {"Ntry.CdtDbtInd", "CdtDbtInd"}})
in
    #"Renamed Columns"

XLSX file:

let
    Source = Excel.Workbook(File.Contents("C:\Users\lacik\OneDrive\Desktop\CURRENT\002 - Template Files\WISE-EUR-statement.xlsx"), null, true),
    #"Balance Statement_Sheet" = Source{[Item="Balance Statement",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"Balance Statement_Sheet", [PromoteAllScalars=true]),
    #"Added Conditional Column" = Table.AddColumn(#"Promoted Headers", "Type", each if [Amount] >= 0 then "Income" else "Expenses"),
    #"Added Custom" = Table.AddColumn(#"Added Conditional Column", "Category", each null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Acc.", each "WIS-EUR"),
    #"Duplicated Column" = Table.DuplicateColumn(#"Added Custom1", "Amount", "AmountCopy"),
    #"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Duplicated Column", {{"Amount", type text}}, "hu-HU"), "Amount", Splitter.SplitTextByDelimiter("-", QuoteStyle.None), {"AmountIn", "AmountOutTemp"}),
    #"Replaced Value" = Table.ReplaceValue(#"Split Column by Delimiter","",each [Currency],Replacer.ReplaceValue,{"Exchange To"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,each [AmountOutTemp],Replacer.ReplaceValue,{"Exchange To Amount"}),
    #"Added Custom2" = Table.AddColumn(#"Replaced Value1", "Amount", each null),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "PerfDate", each null),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Invoice", each null),
    #"Added Custom5" = Table.AddColumn(#"Added Custom4", "⚠️", each null),
    #"Added Custom6" = Table.AddColumn(#"Added Custom5", "AmountRaw", each null),
    #"Added Custom7" = Table.AddColumn(#"Added Custom6", "Project ID", each null),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom7",{{"Date", type date}}, "en-GB"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"AmountIn", type number}, {"Exchange To Amount", type number}, {"Exchange Rate", type number}}),
    #"Merged Columns" = Table.CombineColumns(#"Changed Type1",{"ID", "Description", "Payment Reference"},Combiner.CombineTextByDelimiter(" | ", QuoteStyle.None),"Details"),
    #"Reordered Columns1" = Table.ReorderColumns(#"Merged Columns",{"Date", "PerfDate", "Project ID", "Invoice", "⚠️", "Type", "Category", "Acc.", "AmountIn", "Exchange To Amount", "AmountOutTemp", "Exchange To", "Exchange Rate", "AmountRaw", "Amount", "Details", "AmountCopy", "Running Balance", "Exchange From", "Total Fees", "Payer Name", "Payee Name", "Payee Account Number", "Merchant", "Card Last Four Digits", "Card Holder Full Name", "Attachment", "Note", "Currency"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns1",{"AmountOutTemp", "Total Fees", "Payer Name", "Payee Name", "Payee Account Number", "Merchant", "Card Last Four Digits", "Card Holder Full Name", "Attachment", "Note", "Currency", "Exchange From"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"Exchange To", "Curr."}, {"Exchange Rate", "EXR"}}),
    #"Reversed Rows" = Table.ReverseRows(#"Renamed Columns1"),
    #"Added Index" = Table.AddIndexColumn(#"Reversed Rows", "Index", 1, 1, Int64.Type)
in
    #"Added Index"

Merged Query:

let
    Source = Table.NestedJoin(#"WISE-XLSX", {"Index"}, #"WISE-XML", {"Index"}, "WISE-XML", JoinKind.LeftOuter),
    #"Expanded WISE-XML" = Table.ExpandTableColumn(Source, "WISE-XML", {"IBAN", "Amount", "CdtDbtInd", "DateTime"}, {"WISE-XML.IBAN", "WISE-XML.Amount", "WISE-XML.CdtDbtInd", "WISE-XML.DateTime"})
in
    #"Expanded WISE-XML"