none
Load To Data Model : choose columns/edit query RRS feed

Answers

  • "It would be so much better if, when Loading to the data model, we could choose the columns and also filtering."

    One option would be to create a new query from the base table, where you do the additional column removal and filtering. Then load the new query to the data model.

    Friday, December 9, 2016 3:18 PM

All replies

  • To me this l looks like a receipe for troubles.

    May I ask what your reasons are for choosing this way and not shaping your data in Power Query before loading to the data model? Maybe there is a better alternative for it.


    Imke Feldmann TheBIccountant.com

    Thursday, December 8, 2016 10:08 PM
    Moderator
  • Hi Imke,

    Well i know it would be better to do it in PowerQuery but these columns are used/referenced in other operations.

    So i can't delete them in PowerQuery.

    It would be so much better if, when Loading to the data model, we could choose the columns and also filtering.


    Bertrandr

    Friday, December 9, 2016 9:03 AM
  • "It would be so much better if, when Loading to the data model, we could choose the columns and also filtering."

    One option would be to create a new query from the base table, where you do the additional column removal and filtering. Then load the new query to the data model.

    Friday, December 9, 2016 3:18 PM
  • I agree with Colin - that would have been my recommendation also (& I have no idea why you loose your table name).


    Imke Feldmann TheBIccountant.com

    Friday, December 9, 2016 3:46 PM
    Moderator
  • Hmmm yes it's working except i'm not a huge fan of duplicating "for nothing" queries.

    I mean for nothing because I will have 2 identical queries except for the final exposed columns.


    Bertrandr

    Saturday, December 10, 2016 8:11 PM
  • No, you're not duplicating the query, but reference it instead (check query and rightclick with mouse):


    Imke Feldmann TheBIccountant.com

    Saturday, December 10, 2016 8:16 PM
    Moderator
  • Sorry it's what I meant by duplicating.

    I mean having two queries in my Workbook Queries list.

    In this case i would have to do it for 6 tables and so having 6 references.


    Bertrandr

    Saturday, December 10, 2016 9:20 PM
  • "Well i know it would be better to do it in PowerQuery but these columns are used/referenced in other operations"

    I interpreted this statement to mean that you needed to keep the original query (before choosing columns and filtering) for other operations. When you create a new query from the original, your source step is a reference to the original query. You're not creating a duplicate query.

    Use the referencing technique that Imke suggested and add the additional column select/filter steps to the new referenced query. 

    Please note that from a Power Query standpoint, this is the only sensible option available to you.

    "When in Power Query we choose to Load to the Data Model everything is load and we can't choose and filter columns as we could otherwise (Edit table property in Power Pivot) "

    I'm not sure what you mean by this statement. In Power Pivot, when you edit a table, you are modifying an SQL script and re-querying the source directly. "Load to Data Model" is a push operation. You query, cleanse and transform the source data in Power Query, and push the results to the data model.

    Saturday, December 10, 2016 9:59 PM
  • Sorry it's what I meant by duplicating.

    I mean having two queries in my Workbook Queries list.

    In this case i would have to do it for 6 tables and so having 6 references.


    Bertrandr

    Hi Bertrand,

    I didn't notice this reply when I posted my last one. You can group your main tables and reference tables into separate folders for manageability, but even so, I don't believe that the overall solution presented is what you want. I do hope that you find a solution that works for you.

    Sunday, December 11, 2016 6:25 PM