Append columns in one query in advanced editor

[tthierry]

Good day

I am stuck. I have multiple excel files I want to combine and edit. The number of columns can vary from one file to another.

Each file looks like this:

Analysis
Blabla
For the month ended 31 December 2018
	Dec-18	Nov-18	Oct-18	Sep-18
Income
Revenue 1	£163,219.50	£254,610.46	£311,841.82	£161,455.40
Revenue 2	£8,510.00	£2,300.00	£3,450.00	£0.00
Revenue 3	£6,670.00	£5,558.41	£0.00	£18,898.41
Revenue 4	£9,583.18	£9,583.18	£0.00	£0.00
Total Income	£187,982.68	£272,052.05	£315,291.82	£180,353.81
Less Cost of Sales
Direct Expenses	£0.00	£0.00	£0.00	£0.00
Finance Subsidy	£0.00	£0.00	£0.00	£0.00
Referral Fees	£2,875.00	£0.00	£0.00	£0.00
Total Cost of Sales	£2,875.00	£0.00	£0.00	£0.00
Gross Profit	£185,107.68	£272,052.05	£315,291.82	£180,353.81
Plus Other Income
Interest Income	£0.00	£0.00	£0.00	£0.00
Total Other Income	£0.00	£0.00	£0.00	£0.00
Less Operating Expenses
Rent	£87,852.87	£87,237.62	£87,212.55	£87,688.42
Salaries	£894,026.33	£890,222.36	£909,003.47	£987,168.28
Server Costs	£20,671.48	£9,919.21	£0.00	£10,725.36
Software Subscriptions	£72,939.90	£65,115.53	£71,609.12	£77,063.34
Staff Training	£0.00	£0.00	£0.00	£0.00
Staff Welfare	£19,939.85	£15,811.58	£4,023.85	£20,962.89
Stripe fees	£311.19	£565.80	£350.52	£260.13
Subcontractors	£193,887.47	£203,628.20	£211,600.00	£148,120.00
Subscriptions	£9,200.00	£0.00	£1,656.00	£0.00
Telephone & Internet	£43,868.59	£42,785.29	£46,049.91	£42,603.82
Travel - International	£0.00	£33,507.55	£0.00	£0.00
Travel - National	£9,083.85	£69.00	£2,275.39	£11,504.60
Total Operating Expenses	£1,351,781.53	£1,348,862.14	£1,333,780.81	£1,386,096.84
Net Profit	-£1,166,673.85	-£1,076,810.09	-£1,018,488.99	-£1,205,743.03

What I want to achieve is to combine these files into a single table which would look like this:

PnL Category	Value	Period
Revenue 1	£163,219.50	Dec-18
Revenue 2	£8,510.00	Dec-18
Revenue 3	£6,670.00	Dec-18
Revenue 4	£9,583.18	Dec-18
Total Income	£187,982.68	Dec-18
Direct Expenses	£0.00	Dec-18
Finance Subsidy	£0.00	Nov-18
Referral Fees	£2,875.00	Nov-18
Total Cost of Sales	£2,875.00	Nov-18
Gross Profit	£185,107.68	Nov-18
Interest Income	£0.00	Nov-18
Total Other Income	£0.00	Nov-18
Rent	£87,852.87	Nov-18
Salaries	£894,026.33	Nov-18
Server Costs	£20,671.48	Nov-18
Software Subscriptions	£72,939.90	Nov-18

Currently I am creating individual tables for each period using this code:

let
    Source = Table.SelectColumns(Analysis,{"Column1","Column2"}),
    #"Removed Top Rows" = Table.Skip(Source,4),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Top Rows",{{"Column1", "PnL Category"}, {"Column2", "Value"}}),
    #"GetValueFromCell" = Source{4} [Column2],
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Period", each GetValueFromCell),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([#"Period"] <> null)),
    #"Removed Top Rows1" = Table.Skip(#"Filtered Rows",2),
    #"Filtered Rows1" = Table.SelectRows(#"Removed Top Rows1", each ([PnLValue] <> null)),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows1",{{"Value", type number}, {"Period", type date}})
in
    #"Changed Type"

and I change "Column2" to "Column3" for the second period, to "Column4" for the 3rd period and so on.

Then I append the queries into one new query...

It works but it creates multiple tables and I was hoping to have only one query to transform the file in one table.
I hope it makes sense. Thank you very much.

T

[Greg_Deckler · Accepted solution]

See if you can use the technique here:

https://social.technet.microsoft.com/wiki/contents/articles/32915.power-bi-merge-query-with-m.aspx

I use that technique to combine multiple queries into one using Append here:

https://community.powerbi.com/t5/Community-Blog/Turning-quot-Web-by-Example-quot-into-Power-Query-Fu...

[tthierry]

Hi @Greg_Deckler 

Firstly, THANK YOU! As I progressed massively with your resources.

Secondly, I am almost there but not quite yet. The Appended query function doesn't work and I get the error message:

Expression.Error: We cannot convert a value of type Table to type List.
Details:
Value=Table
Type=Type

My code is

let
    Source = Excel.Workbook(File.Contents("C:\Users\Blabla/Downloads\Profit and Loss[64].xls"), null, true),
    #"Profit and Loss1" = Source{[Name="Profit and Loss"]}[Data],
    #"Selected Columns"= Table.SelectColumns(#"Profit and Loss1",{"Column1","Column2"}),
    #"Removed Top Rows" = Table.Skip(#"Selected Columns",4),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Top Rows",{{"Column1", "PnLCategory"}, {"Column2", "PnLValue"}}),
    #"GetValueFromCell" = #"Renamed Columns"{0} [PnLValue],
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Period", each GetValueFromCell),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([#"Period"] <> null)),
    #"Removed Top Rows1" = Table.Skip(#"Filtered Rows",2),
    #"Filtered Rows1" = Table.SelectRows(#"Removed Top Rows1", each ([PnLValue] <> null)),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows1",{{"PnLValue", type number}, {"Period", type date}}),
    #"Extracted Table from Source1" = Table.SelectColumns(#"Profit and Loss1",{"Column1", "Column3"}),
    #"Removed Top Rows2" = Table.Skip(#"Extracted Table from Source1",4),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Top Rows2",{{"Column1", "PnLCategory"}, {"Column3", "PnLValue"}}),
    #"GetValueFromCell1" = #"Renamed Columns1"{0} [PnLValue],
    #"Added Custom1" = Table.AddColumn(#"Renamed Columns1", "Period", each GetValueFromCell1),
    #"Filtered Rows2" = Table.SelectRows(#"Added Custom1", each ([#"Period"] <> null)),
    #"Removed Top Rows3" = Table.Skip(#"Filtered Rows2",2),
    #"Filtered Rows3" = Table.SelectRows(#"Removed Top Rows3", each ([PnLValue] <> null)),
    #"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows3",{{"PnLValue", type number}, {"Period", type date}}),
    #"Appended Query" = Table.Combine( #"Changed Type", #"Changed Type1")
    in
    #"Appended Query"

When I get to #"Changed Type", I have a table in the right format as below:

PnLCategory	PnLValue	Period
Revenue1	450	01/11/2018
Revenue2	500	01/11/2018
Item1	230	01/11/2018
Item2	24	01/11/2018
Item3	52	01/11/2018

And when I get to #"Changed Type1", I have another table in the right format as below:

PnLCategory	PnLValue	Period
Revenue1	492	01/12/2018
Revenue2	542	01/12/2018
Item1	272	01/12/2018
Item2	66	01/12/2018
Item3	94	01/12/2018

I want the #"Appended Query" step to give me:

PnLCategory	PnLValue	Period
Revenue1	450	01/11/2018
Revenue2	500	01/11/2018
Item1	230	01/11/2018
Item2	24	01/11/2018
Item3	52	01/11/2018
Revenue1	492	01/12/2018
Revenue2	542	01/12/2018
Item1	272	01/12/2018
Item2	66	01/12/2018
Item3	94	01/12/2018

What have I missed please? Thanks in advance.

T

[tthierry]

And what I missed was {}

#"Appended Query" = Table.Combine( {#"Changed Type", #"Changed Type1"})

Thanks a lot!!!