Changing Dates on Columns

[Anonymous]

Hi!

I've got a query to a file folder which contains Excel files with projected forecasts. Each file contains dates in columns and the values in rows. The problem is that the dates in the columns on each file are different, as it is a different forecast period. when new files are added, the same columns will contain different dates, as it is a new forecast range. So the furthest left column on file 1 might be with date 43920, but on file two this will be 43948. If they were all the same dates, I would just promote the headers and then get rid of the other dates, but because of the above issue, I cannot do that.

Does anybody know how I could either merge the files so that each new file was added to the right of the previous files instead of appending below, or a different way around this problem?

Many thanks,

Paddy

[dax]

Hi @Anonymous , 

It seems that you want to combine two excel together, right? If so, and if you want to make the similar columns from two excels into the same column, you need to make sure the column names, column types , column amounts  be the same, then when you append table, it will show in the same columns. By the way, you also could post your sample and your expected output, then I will test this in my environment.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @dax 

Thanks for responding. 

I would send the data but I can't see a way to attach Excel files, sorry! If there is a way to send Excel files I will glady do it.

But basically, the column names are never the same because each new file is always the latest forecast, and so once a date is in the past, it will no longer be a column name like it was on a previous file.

So at the start of June, you would get a table like the below, with the columns starting from June and going into the future. This is forecasted order quantity by product (name) and customer.

Quantity		Date
Customer	Name	01/06/2020	08/06/2020	15/06/2020	22/06/2020	29/06/2020	06/07/2020
C1	P1	138	181	209	237	265	265
	P2	1,325	1,786	2,060	2,334	2,607	2,607
	P3	848	1,128	1,301	1,474	1,646	1,646
	P4	1,176	1,586	1,829	2,072	2,314	2,314
	P5	963	1,271	1,465	1,660	1,854	1,854
	P6	303	422	487	552	617	617
	P7	144	185	213	242	270	270
	P8	0	0	0	0	0	0
C1 Total	4,897	6,559	7,564	8,571	9,573	9,573
C2	P1	40	40	40	40	40	40
	P2	30	30	30	30	30	30
	P3	40	40	40	40	40	40
	P4	40	40	40	40	40	40
	P5	40	40	40	40	40	40
	P6	40	40	40	40	40	40
	P7	55	55	55	55	55	55
C2 Total	285	285	285	285	285	285

But in an earlier file from the end of March, the date columns will start at the end of March and continue from that point.

Quantity		Date
Customer	Name	30/03/2020	06/04/2020	13/04/2020	20/04/2020	27/04/2020	04/05/2020	11/05/2020
C1	P1	300	300	300	300	300	300	300
	P2	332	332	332	332	249	249	249
	P3	166	166	249	332	166	83	249
	P4	249	249	249	249	249	83	249
	P5	249	249	249	249	249	83	249
	P6	83	166	83	83	83	144	166
	P7	87	87		87	174	87	87
	P8	81	148	74	74	148	81	148
C1 Total	1,547	1,697	1,536	1,706	1,618	1,110	1,697
C2	P1	40	40	40	40	40	40	40
	P2	47	47	47	47	47	47	47
	P3	40	40	40	40	40	40	40
	P4	40	40	40	40	40	40	40
	P5	26	26	26	26	26	26	26
	P6	26	26	26	26	26	26	26
	P7	26	26	26	26	26	26	26
C2 Total	245	245	245	245	245	245	245

If all the dates were the same, I would promote the headers so all the dates were columns, and then could unpivot all the value columns and have a 'dates' column and 'values' column, but because 'column 3' will contain different dates due to the issue described above, this will not work.

I could manually edit each file to insert a number of blank columns so that the dates were the same, but was hoping there would be a way to solve the problem in Power Query.

Hope this makes sense.

Thanks!

[camargos88 · Accepted solution]

Hi @Anonymous ,

You can edit the Transform Sample File to handle it, like:

let
    Source = Excel.Workbook(Parameter1, null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Filled Down" = Table.FillDown(#"Promoted Headers",{"Quantity"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each not Text.Contains([Quantity], "Total")),
    Row = Table.UnpivotOtherColumns(Table.Skip(Table.DemoteHeaders(Table.Skip(#"Filtered Rows",1)), 1), {"Column1", "Column2"}, "Attribute", "Value"),
    Header = Table.UnpivotOtherColumns(Table.Skip(Table.DemoteHeaders(Table.FirstN(#"Filtered Rows",1)),1), {"Column1", "Column2"}, "Attribute", "Value"),
    Merge = Table.NestedJoin(
            Table.UnpivotOtherColumns(Table.Skip(Table.DemoteHeaders(Table.Skip(#"Filtered Rows",1)), 1), {"Column1", "Column2"}, "Attribute", "Value"), 
            "Attribute", 
            Table.UnpivotOtherColumns(Table.Skip(Table.DemoteHeaders(Table.FirstN(#"Filtered Rows",1)),1), {"Column1", "Column2"}, "Attribute", "Value"), 
            "Attribute", 
            "Header", 
            JoinKind.Inner
            ),
    #"Expanded Header" = Table.ExpandTableColumn(Merge, "Header", {"Value"}, {"Value.1"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Header",{"Attribute"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Value.1", "Date"}, {"Column1", "Customer"}, {"Column2", "Name"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Customer", "Name", "Date", "Value"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"Customer", type text}, {"Name", type text}, {"Date", type date}, {"Value", Int64.Type}})
in
    #"Changed Type"

Check the steps Row and Header, the step Merge are skipping them and using only the code, you can delete the Row and Header once it's ok.

[Anonymous]

Hi @camargos88 ,

It looks like your solution has worked perfectly, and I didn't even need to know M-Code! Really appreciate your help on that, thank you.

All the best, 

Paddy

[Anonymous]

Hi @camargos88 ,

Thanks for your response! I'm not the best with M-code but I'll give it a shot and let you know how it goes.

Cheers,

Paddy