Forum Discussion

Anonymous

Not applicable

6 years ago

Solved

Changing Dates on Columns

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...

Need Help

camargos88

6 years ago

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

Not applicable

6 years ago

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

Community Champion

6 years ago

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
Not applicable
6 years ago
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
Anonymous
Not applicable
6 years ago
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

Forum Discussion

Changing Dates on Columns

Related content

Date between slicer - Change order

Change random dates

Find Most Recent Date Relative to another Date Column in Power Query

Progressive Date Columns

Generate a column for dates between start dates (list/ pivot?)