Preppin’ Data 2023 Week Two – With Power Query!

The Preppin’ Data 2023 Week Two Challenge provided some ways to practice common data prep techniques like joins and merging columns. The data was related to banking and the end result is a table with International Banking Account Numbers.

Tip for Merging Columns: When you merge multiple columns, the data types for all the columns should be text. Power Query on import will try to guess the data types for each column. There is one column that Power Query guess was a number, but later on you need to merge that column.

Personally, I go back to the “Changed Types” step at the beginning and edit it so that it is all done in one step in these scenarios, rather than making a new step.

Here is the video walkthrough using Power Query in Excel:

Here is my M code:

let
    Source = #"Transactions Input",
    
    
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    
    
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Transaction ID", Int64.Type}, {"Account Number", type text}, {"Sort Code", type text}, {"Bank", type text}}),
    // Removed hyphens from sort code column
    #"Removed Hyphens" = Table.ReplaceValue(#"Changed Type","-","",Replacer.ReplaceText,{"Sort Code"}),
    // Merged on bank on Swift Codes table
    #"Merged with Swift Codes" = Table.NestedJoin(#"Removed Hyphens", {"Bank"}, #"Swift Codes", {"Bank"}, "Swift Codes", JoinKind.LeftOuter),
    
    // Expanded Check Digits and Swift Code
    #"Expanded Swift Codes" = Table.ExpandTableColumn(#"Merged with Swift Codes", "Swift Codes", {"SWIFT code", "Check Digits"}, {"SWIFT code", "Check Digits"}),
    
    // Added Country Code which were all GB
    #"Added Country Code" = Table.AddColumn(#"Expanded Swift Codes", "Country Code", each "GB", type text),
    // Made the IBAN Column
    #"Merged IBAN Column" = Table.CombineColumns(#"Added Country Code",{"Country Code", "Check Digits", "SWIFT code", "Sort Code", "Account Number"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"IBAN"),
    
    // Choose the columns you want
    #"Removed Other Columns" = Table.SelectColumns(#"Merged IBAN Column",{"Transaction ID", "IBAN"})

in

    #"Removed Other Columns"
Bitnami