Power Query (noob-ish) struggling with changing Field names.
Results 1 to 4 of 4

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

  1. #1
    Board Regular tbablue's Avatar
    Join Date
    Apr 2007
    Posts
    450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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=#1 D2228]let[/COLOR]
    [COLOR=#1 D2228] Source = Excel.Workbook(File.Contents("C:\Users\xxxxx\Documents\Bookings\Contract Bookings\20190718.xlsm"), null, true),[/COLOR]
    [COLOR=#1 D2228] #"1" = Source{[Name="20190718"]}[Data],[/COLOR]
    [COLOR=#1 D2228] #"Removed Top Rows" = Table.Skip(#"1",4),[/COLOR]
    [COLOR=#1 D2228] #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),[/COLOR]
    [COLOR=#1 D2228] #"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}}),[/COLOR]
    [COLOR=#1 D2228] #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Client Code", "CandID", "Forename", "Surname", "Pay Type", "Site", "Grand Total", "Column25", "TableName"}, "Booking Date", "Booked Time")[/COLOR]
    [COLOR=#1 D2228]in[/COLOR]
    [COLOR=#1 D2228] #"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.
    [/COLOR]
    dOH!

  2. #2
    Rules violation
    Join Date
    Jan 2012
    Posts
    912
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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"

  3. #3
    Board Regular tbablue's Avatar
    Join Date
    Apr 2007
    Posts
    450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Really grateful for your time and knowledge.

    I get the following error

    Expression.Error: The column 'Column1' of the table wasn't found.
    Details:
    Column1

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

    Regards
    dOH!

  4. #4
    Rules violation
    Join Date
    Jan 2012
    Posts
    912
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    I can't see Column1 anywhere in that code. Which step errors?
    Last edited by theBardd; Aug 1st, 2019 at 04:05 AM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •