February 26, 2016 at 7:02 pm #3518
- 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.February 26, 2016 at 11:09 pm #3520
- Started: 7
- Replies: 2556
- Total: 2563
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.
Attachments:You must be logged in to view attached files.
You must be logged in to reply to this topic.