Power Pivot vs. Power Query

[sjhc1177Participant Started: 25Replies: 28Total: 53]

Hi, I’m building a new database from scratch using Power Query to pull our data from SQL and then putting it into Power Pivot and down to excel in pivot tables and cube functions, values…

What is the best practice for size and memory? Example I have a calendar I need, that will have DATES/ WEEKS/FINANCIAL YEAR/ WTD (Starting Mondays)/YTD(starting Feb) and so on.

Would I be best to build a tab in excel putting dates 2/1/17-1/31/21, then a formula to list what week each date is…

Or build the formulas in POWER QUERY or POWER PIVOT?

 

Thanks

[tomallanKeymaster Started: 0Replies: 417Total: 417]

Hi,

Most calendar table implementations that I have seen create the table in Power Query.

Rob Collie and Avi Singh created a clever Power Query for a calendar table that created start and end dates from a data table (see chapter 20 of Power Pivot and Power BI).

Ken Puls created an interesting implementation for a calendar table that uses parameters created in Excel (see chapter 24 of M is for Data Monkey).

Since you are using SQL Server as the data source, I would encourage you to consider using that resource to create the calendar table for you. I wrote a post a while back for how to do that.

Tom

[sjhc1177Participant Started: 25Replies: 28Total: 53]

Thanks for the info. I will read up on the great reading.

Question since you seem to know a lot.

I would like to write a POWER QUERY statement and tell it to trim not the left 4 characters. But if it sees “$” use the RIGHT 3 of that, or if it sees “PC” use the 2 to the left.

This is what i currently use:

= Sql.Database(“SERVER”, “DB0001_160517195223”, [Query=”SELECT LEFT([disc_code], 4) AS [POS DISCOUNT AMOUNT]#(lf)FROM tbldiscount#(lf)GROUP BY LEFT([disc_code], 4); “])

Thanks

[Author]

Posts