none
Pivot Table does not find custom column from power query RRS feed

  • Question

  • Hello everyone,

    I got a minor problem with power query and didnt find a solution yet, maybe someone can help.

    I created a query, where I add 2 custom columns in the end. After the output I want to convert the table into a pivot table to make some reports with it. The problem is, that in the pivot table settings I can find any column but the 2 custom collumns.

    The two custom columns are included in the defined table, but cant be found while creating a pivottable.

    Can anyone help me on that? Or maybe had the same issue?

    Thanks in advance!

    Thursday, June 29, 2017 1:51 PM

Answers

  • I have never had such a problem. What I would try is this:

    1) Change the name of the last added column

    2) Change the last stepname

    Maybe for some reason the last commands are ignored because they are mistaken for some other code (I know this sounds strange, but I have no other idea :-) )


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    • Marked as answer by nicknamemaker Wednesday, July 5, 2017 10:59 AM
    Friday, June 30, 2017 7:59 PM
    Moderator

All replies

  • This sounds really strange. Any chance that you're referencing the wrong query here?

    Imke Feldmann - Website: http://www.TheBIccountant.com , Twitter: @TheBIccountant

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Thursday, June 29, 2017 5:11 PM
    Moderator
  • Excel 2016 Pro Plus with PowerPivot and Power Query (aka Get & Transform)
    This sounds really familiar.
    Here is the error message I get when adding a column to a PQ query loaded as a Table to a SS
    and then trying to also load the query to the Data Model:
    "Enabling Load to Data Model will remove and recreate the table for
    this query in the worksheet. Any customizations or references you
    made to the table will be lost."
    A workaround is to add the column as a step in PQ
    or if the function is not available in PQ,
    use the "Reverse-Linked Table" operation
    as explained in chapter 14 of the book:
    "Microsoft Excel2013, Building Data Models with PowerPivot"
    by Ferrari & Russo.


    Thursday, June 29, 2017 8:04 PM
  • Hi Imke,

    unfortunately I cant post the query because it is from my employer. The query itself is just an import from another file. After the data import I delete some columns. Last step I add two columns with the command "Text.Start([Column A],4)" to get only the first 4 letters of certain columns. Thats basicly the query.

    When using the output for a pivot table it does not find the columns in the pivot options.

    I found a quick fix, where I add another empty column in the end, so I can manually only chose a part auf the table (without the empty column). With this solution pivot finds the colums.

    I could live with it, but I still find it very strange :)

    Nick

    Friday, June 30, 2017 7:21 AM
  • I have never had such a problem. What I would try is this:

    1) Change the name of the last added column

    2) Change the last stepname

    Maybe for some reason the last commands are ignored because they are mistaken for some other code (I know this sounds strange, but I have no other idea :-) )


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    • Marked as answer by nicknamemaker Wednesday, July 5, 2017 10:59 AM
    Friday, June 30, 2017 7:59 PM
    Moderator
  • Hi Imke,

    changing the stepname does actually solve the problem!

    Thank you.

    Nick

    Wednesday, July 5, 2017 11:00 AM