Forum Discussion
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 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
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.
5 Replies
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 ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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!
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.
