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

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

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:

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,306
Messages
5,510,507
Members
408,792
Latest member
S_s_s

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top