Power Query help

[shophoney]

Hi,

I have a power query that lists all accounts per transaction.

But I need to know if the invoice was taxable. So if there are three lines being, PURCHASES, SHIPPING AND TAX. I want to know every transaction that has the TAX account.

Example this transaction doesn't have the tax account in the list. So it's non taxable on each line.

But if one of the accounts listed was TAX then I want the word tax on each line. Each line is the same document#.

 

[Kerryx]

Dunno if this is what you are looking for but here is my attempt

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Vendor", type text}, {"Balance", type any}, {"Account", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "TaxC", each if [Account] = "Tax" then "Tax" else null ),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "AccountSor", each if[Account]="Tax" then 1 else if[Account]="Ship" then 2 else if[Account]="Pur" then 3 else 4),
    #"Sorted Rows" = Table.Sort(#"Added Custom1",{{"Vendor", Order.Ascending}, {"AccountSor", Order.Ascending}}),
    #"Filled Down" = Table.FillDown(#"Sorted Rows",{"TaxC"}),
    #"Replaced Value" = Table.ReplaceValue(#"Filled Down",null,"No Tax",Replacer.ReplaceValue,{"TaxC"})
in
    #"Replaced Value"