Home Forums Power Pivot BMP for shaping data: Power Pivot or Power Query

This topic contains 1 reply, has 2 voices, and was last updated by  tomallan 2 years, 11 months ago.

Viewing 2 posts - 1 through 2 (of 2 total)
  • Author
    Posts
  • #3518

    jmg80525
    Participant
    • Started: 14
    • Replies: 10
    • Total: 24

    Need some direction on best management practice for shaping data.  I’m currently using Power Query as an ETL tool.  I coalesce a bunch of desperate excel workbooks into a single table (HT to Ken Puls for this post) and generally shape the data on a first order basis (i.e data types, split columns, etc.)  I then load the data into a PP data model and there I calculate some columns.   As an example, here is a DAX formula I utilize to normalize calendar date to production month (= RANKX ( FILTER (ProdMonth, [Entity] = EARLIER ( [Entity] ) ), [YearMonth], , 1, DENSE )) (HT to Tom for helping me with this in an earlier post.).  I then utilize Power Update to move everything into a SQL Server instance.

    So in short, this is a pure ETL process where I am utilizing tools in the Power Pivot domain combined with Power Update instead of relying on SSIS. (HT to Matt Arlington on his post .)    One of the tables in the data model results in 40 million plus rows.  With a 32 GB ram PC , some finger-crossing and time I can refresh the data model.

    From a data model refresh optimization standpoint, am I better to shape data in Power Query or Power Pivot?  From a neatness stand-point my preference is Power Query.  I could share out the query to the organization via the Data Gateway and another person has everything they need to replicate the ETL process.    Can one easily replicate the above-mentioned DAX formula with M?  Why shouldn’t I think of Power Query strictly as shaping data and Power Pivot for measuring?  Thanks in advance.

    #3520

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2552
    • Total: 2559

    I agree that Power Pivot is for measuring and Power Query is for shaping data.

    Regarding equivalent M code for the RANKX formula, please see attached workbook. The attribution is due to a participant, SuddenlySubtle, on answers.microsoft.com, who posted his solution just 8 days ago.

    Tom

    Attachments:
    You must be logged in to view attached files.
Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic.