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

tbablue

Active Member
Joined
Apr 29, 2007
Messages
450
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]
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

theBardd

Rules violation
Joined
Jan 21, 2012
Messages
912
Try this

Code:
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

Active Member
Joined
Apr 29, 2007
Messages
450
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

Rules violation
Joined
Jan 21, 2012
Messages
912
I can't see Column1 anywhere in that code. Which step errors?
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,102,735
Messages
5,488,557
Members
407,646
Latest member
utl1095

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top