PQ - From trash to 1NF

From trash to 1NF

Number	Date	On Hold	Total		Company	Number	Date	On Hold	Total
Currency:					AME03 AMERICAN EXPRESS	ER_AMEX_2019-2020_7	2/25/2020	N	13691.4
AME03 AMERICAN EXPRESS					AME03 AMERICAN EXPRESS	ER_AMEX_2019-2020_8	3/25/2020	N	6360.78
ER_AMEX_2019-2020_7	2/25/2020	N	13,691.40		AMG01 AMERIGAS PROPANE	804635113	3/30/2020	N	179.25
ER_AMEX_2019-2020_8	3/25/2020	N	6,360.78
20,052.18	6,725.00	0	0
AMG01 AMERIGAS PROPANE
804635113	3/30/2020	N	179.25
179.25	179.25	0	0

Code:

---

let
    Source = Excel.CurrentWorkbook(){[Name="Table13"]}[Content],
    IF = Table.AddColumn(Source, "Company", each if [Date] = null then [Number] else null),
    Fill = Table.FillDown(IF,{"Company"}),
    Filter = Table.SelectRows(Fill, each ([Date] <> null) and ([On Hold] = "N")),
    TSC = Table.SelectColumns(Filter,{"Company", "Number", "Date", "On Hold", "Total"})
in
    TSC

---