Power Query (noob-ish) struggling with changing Field names.

[tbablue]

Hi Forum,

Long time excel user - first post with a Power Query question.

I've been gnawing on this problem for days - my head is going numb. Any help offered, gratefully received.

Every week, I get a cross tabulated report which I need to unpivot:
Ancillary information like Name, Dept,etc - are in columns 1 thru 6.
Columns 7 thru 23 are headed by dates (these change every week).



This is my code

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] D2228][FONT=&quot]let[/FONT][/COLOR]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] D2228][FONT=&quot] Source = Excel.Workbook(File.Contents("C:\Users\xxxxx\Documents\Bookings\Contract Bookings\20190718.xlsm"), null, true),[/FONT][/COLOR]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] D2228][FONT=&quot] #"1" = Source{[Name="20190718"]}[Data],[/FONT][/COLOR]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] D2228][FONT=&quot] #"Removed Top Rows" = Table.Skip(#"1",4),[/FONT][/COLOR]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] D2228][FONT=&quot] #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),[/FONT][/COLOR]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] D2228][FONT=&quot] #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Client Code", type text}, {"CandID", Int64.Type}, {"Forename", type text}, {"Surname", type text}, {"Pay Type", type text}, {"Site", type text}, {"22/03/2019", type number}, {"29/03/2019", type number}, {"05/04/2019", type number}, {"12/04/2019", type number}, {"19/04/2019", type number}, {"26/04/2019", type number}, {"03/05/2019", type number}, {"10/05/2019", type number}, {"17/05/2019", type number}, {"24/05/2019", type number}, {"31/05/2019", type number}, {"07/06/2019", type number}, {"14/06/2019", type number}, {"21/06/2019", type number}, {"28/06/2019", type number}, {"05/07/2019", type number}, {"12/07/2019", type number}, {"Grand Total", type number}, {"Column25", type number}, {"TableName", type text}}),[/FONT][/COLOR]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] D2228][FONT=&quot] #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Client Code", "CandID", "Forename", "Surname", "Pay Type", "Site", "Grand Total", "Column25", "TableName"}, "Booking Date", "Booked Time")[/FONT][/COLOR]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] D2228][FONT=&quot]in[/FONT][/COLOR]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] D2228][FONT=&quot] #"Unpivoted Columns"


My issue is that I need to redo this query every week for a new worksheet (this instance is 20190718 - next week will be 20190725) - also, the dates increment by a week in columns 7 thru 23 which throws an error.

Is it possible to unpivot columns by using something like their ordinal position {6,7,8 ... 20,21,22} instead of their explicit headers?

I appreciate this is a bit of a ramble.

Any help gratefully rec'd.[/FONT][/COLOR]

 

[theBardd]

Try this

let
  Source = Excel.Workbook(File.Contents("C:\Users\xxxxx\Documents\Bookings\Contract Bookings\20190718.xlsm"), null, true),
  #"1" = Source{[Name="20190718"]}[Data],
  Cols = Table.ColumnNames(#"1"),
  ColsUnpivot=List.FirstN(Cols, 9),
  #"Removed Top Rows" = Table.Skip(#"1",4),
  #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
  #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Client Code", type text}, {"CandID", Int64.Type}, {"Forename", type text}, {"Surname", type text}, {"Pay Type", type text}, {"Site", type text}, {"22/03/2019", type number}, {"29/03/2019", type number}, {"05/04/2019", type number}, {"12/04/2019", type number}, {"19/04/2019", type number}, {"26/04/2019", type number}, {"03/05/2019", type number}, {"10/05/2019", type number}, {"17/05/2019", type number}, {"24/05/2019", type number}, {"31/05/2019", type number}, {"07/06/2019", type number}, {"14/06/2019", type number}, {"21/06/2019", type number}, {"28/06/2019", type number}, {"05/07/2019", type number}, {"12/07/2019", type number}, {"Grand Total", type number}, {"Column25", type number}, {"TableName", type text}}),
  #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", ColsUnpivot, "Booking Date", "Booked Time")
 in
  #"Unpivoted Columns"

 

[tbablue]

Really grateful for your time and knowledge.

I get the following error

[FONT=&quot]Expression.Error: The column 'Column1' of the table wasn't found.[/FONT]
[FONT=&quot]Details:[/FONT]
[FONT=&quot] Column1

I'm trying to identify the issue - but any further help would be most welcome.

Regards[/FONT]

 

[theBardd]

I can't see Column1 anywhere in that code. Which step errors?