none
Index Key using powerquery to create a consolidation table RRS feed

  • Question

  • I have a workbook which has data for different projects. Each project has categories and monthly data. Using powerquery an index column was created in order to define the relationship between the monthly data and categories for each project.

    My Steps:

    1. Create a categorykey with index
    2. Categories merged with categorykey
    3. Monthly data merged with category key
    4. Powerpivot relationship between categories and monthly data

    Most of the categories are generic however some projects have additional categories or less thus the index numbers will never be the same across all the projects for their respective categories.

    My issue now is that I am trying to create a consolidation of the data for all the projects . My thought process was to create an appended Category table(powerquery) and remove any duplicate categories. 

    But I can not create a relationship of my appended categories with my individual project categories due to duplicate index numbers and If I create a relationship using a custom index (Index1.1) column to my individual project table categories, the index numbers will not match to the correct category as per the individual tables.


    Example.
    Project A revenue key : Individual Index number: 34  Appended Custom Index Number: 58

    Project B subcontracting: individual index number:34 Appended Custom Index Number:34

    If I create a consolidated pivot using the appended custom index my revenue for project A will be shown under the subcategory subcontracting. My appended Index is related to the individual category tables.

    I am just looking for some advise as i am not sure if this is even possible to do.

    I was't allowed to upload the link to my drop-box file of the model.

    Thursday, March 23, 2017 8:09 AM

Answers

  • I cannot imagine how you can create meaningful keys using an Index-function in Power Query. It's normally just returning the row-number and has no relation to the actual data in that row.

    As a new user it seems to take a little while before you are able to upload links or images, but once this is possible, pls provide more details that easily explains the business logic behind it.


    Imke Feldmann TheBIccountant.com

    Thursday, March 23, 2017 5:35 PM
    Moderator

All replies

  • I cannot imagine how you can create meaningful keys using an Index-function in Power Query. It's normally just returning the row-number and has no relation to the actual data in that row.

    As a new user it seems to take a little while before you are able to upload links or images, but once this is possible, pls provide more details that easily explains the business logic behind it.


    Imke Feldmann TheBIccountant.com

    Thursday, March 23, 2017 5:35 PM
    Moderator
  • I cannot imagine how you can create meaningful keys using an Index-function in Power Query. It's normally just returning the row-number and has no relation to the actual data in that row.

    As a new user it seems to take a little while before you are able to upload links or images, but once this is possible, pls provide more details that easily explains the business logic behind it.


    Imke Feldmann TheBIccountant.com


    Hi Snova001,

    Is this still an issue? If so, are you now able to illustrate the problem with some test data?


    Regards,

    Michael Amadi

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

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    Tuesday, May 16, 2017 9:07 PM
    Moderator