First Row as a column

[anillinda]

I have the following data :

Vendor name as the first row
The second row has the invoice number, amount and the amount outstanding.
The third, fourth and so on have the invoice numbers and then there is another vendor and invoice numbers.

I need to put the vendor number against the invoice number.
I would appreciate the help.

Ram01 RAM Enterprises
Invoice#02021	2530	10/15/2019
Invoice#02022	2680	11/20/2019

Thanks
Neal

 

[COwen]

When you say

put the vendor number against the invoice number.

do your mean something like:

Invoice#020212530
2530Invoice#02021

Or something different?

 

[sandy666]

something like this?

Invoice	Amount	Date		Invoice	Amount	Date
Ram01 RAM Enterprises				Ram01 RAM Enterprises
Invoice#02021	2530	15/10/2019		#02021Invoice	2530	15/10/2019
Invoice#02022	2680	20/11/2019		#02022Invoice	2680	20/11/2019
Company ABC				Company ABC
Invoice#1004	3000	25/12/2020		#1004Invoice	3000	25/12/2020
Invoice#1005	4000	26/12/2020		#1005Invoice	4000	26/12/2020

 

[anillinda]

I want the data to look like as follows:

Ram01 RAM Enterprises	Invoice#2021	2530	15/10/2019
Ram01 RAM Enterprises	Invoice#2022	2680	02/11/2019
ABC01 ABC Corporation	Invoice#1058952	35780	02/15/2019
ABC01 ABC Corporation	Invoice#105898	356987	03/15/2019

 

[sandy666]

I assume it will be correct

Invoice	Amount	Date		Vendor	Invoice	Amount	Date
Ram01 RAM Enterprises				Ram01 RAM Enterprises	Invoice#02021	2530	15/10/2019
Invoice#02021	2530	15/10/2019		Ram01 RAM Enterprises	Invoice#02022	2680	20/11/2019
Invoice#02022	2680	20/11/2019		Company ABC	Invoice#1004	3000	25/12/2020
Company ABC				Company ABC	Invoice#1005	4000	26/12/2020
Invoice#1004	3000	25/12/2020
Invoice#1005	4000	26/12/2020

so try

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    IF = Table.AddColumn(Source, "Vendor", each if [Amount] = null then [Invoice] else null),
    FillD = Table.FillDown(IF,{"Vendor"}),
    Filter = Table.SelectRows(FillD, each ([Amount] <> null)),
    TSC = Table.SelectColumns(Filter,{"Vendor", "Invoice", "Amount", "Date"}),
    Type = Table.TransformColumnTypes(TSC,{{"Date", type date}})
in
    Type

 

[sandy666]

or more flexible

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    IF = Table.AddColumn(Source, "Vendor", each if not Text.Contains([Invoice], "Invoice") then [Invoice] else null),
    FillD = Table.FillDown(IF,{"Vendor"}),
    Filter = Table.SelectRows(FillD, each Text.Contains([Invoice], "Invoice")),
    TSC = Table.SelectColumns(Filter,{"Vendor", "Invoice", "Amount", "Date"}),
    Type = Table.TransformColumnTypes(TSC,{{"Date", type date}})
in
    Type

 

[anillinda]

Expression.Error: The column 'Invoice' of the table wasn't found.
Details:
Invoice

 

[sandy666]

look at the headers in post#5 (blue table: source, green table: result)
you cannot copy/paste "as is" but adapt M to your header names

 

[anillinda]

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

 

[anillinda]

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