Excel Power Query Transforming Multiple Columns based on a Single Condition

[Syndicate_Admin]

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!

[Syndicate_Admin]

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.

[ronrsnfld]

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"

[ronrsnfld]

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.