Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I am working with an ODBC query that is pulling customer invoice information.
Some invoice types do not store billing information since they are more or less automatic.
What I am wanting to do is if a record's [BillToName] is null then I want to replace all of the billing address columns with the customer's main address..
So if [BillToName] = null then
[BillToName] = [CustomerName] and
[BillToAddress1] = [AddressLine1] and
[BillToAddress2] = [AddressLine2] and etc.....
It's important to note that only 1 column is being checked (BillToName). I can't mix/match each individual column where some parts of the address somehow was there so it didn't get replaced and the other columns that were null did get replaced to where the billing address has pieces of both addresses.
Thanks for any help you can provide!
Sure.
Sample Data Table
CustomerNo | CustomerName | InvoiceNo | AddressLine1 | AddressLine2 | AddressLine3 | City | State | ZipCode | CountryCode | BillToName | BillToAddress1 | BillToAddress2 | BillToAddress3 | BillToCity | BillToState | BillToZipCode | BillToCountryCode |
ABC001 | ABC Company, Inc | 1234567 | 123 Main Street | My City | ST | 11123-1234 | USA | ||||||||||
ABC002 | ABC Company, Inc d/b/a MyCo | 9876543 | 123 Main Street | My City | ST | 11123-1234 | USA | MyCo | 900 Any St | Suite 200 | El City | ZZ | 55123-9898 | USA |
For each record that contains a null [BillToName], every portion of the "BillTo" address is replaced with the Customer's main address:
{[CustomerName], [AddressLine1], [AddressLine2], etc}
I have attached a sample workbook containing the above with a local table that has been pulled into Power Query as well.
Here's one way:
let
Source = Excel.CurrentWorkbook(){[Name="Table26"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{
{"CustomerNo", type text}, {"CustomerName", type text}, {"InvoiceNo", Int64.Type},
{"AddressLine1", type text}, {"AddressLine2", type any}, {"AddressLine3", type any},
{"City", type text}, {"State", type text}, {"ZipCode", type text},
{"CountryCode", type text}, {"BillToName", type text}, {"BillToAddress1", type text},
{"BillToAddress2", type text}, {"BillToAddress3", type any},
{"BillToCity", type text}, {"BillToState", type text}, {"BillToZipCode", type text},
{"BillToCountryCode", type text}}),
//Fill in if BillTo is Blank
#"Bill To" =
Table.FromRecords(
Table.TransformRows(#"Changed Type", (r)=>
if r[BillToName] = null or r[BillToName] = "" or r[BillToName] = " " then
Record.TransformFields(r,{
{"BillToName", each r[CustomerName]},
{"BillToAddress1", each r[AddressLine1]},
{"BillToAddress2", each r[AddressLine2]},
{"BillToAddress3", each r[AddressLine3]},
{"BillToCity", each r[City]},
{"BillToState", each r[State]},
{"BillToZipCode", each r[ZipCode]},
{"BillToCountryCode", each r[CountryCode]}
}) else r)
),
//Being lazy, I made them all type text, but you can be more specific if necessary
#"Changed Type2" = Table.TransformColumnTypes(#"Bill To",
List.Transform(Table.ColumnNames(#"Bill To"), each {_, type text}))
in
#"Changed Type2"
Perhaps if you provide a textual (not a picture) data sample that illustrates your problem, and a screenshot of your expected results, focused assistance could be more easily provided.