Home Forums Power Pivot Power Pivot vs. Power Query

This topic contains 2 replies, has 2 voices, and was last updated by  sjhc1177 3 years, 5 months ago.

Viewing 3 posts - 1 through 3 (of 3 total)
  • Author
    Posts
  • #18140

    sjhc1177
    Participant
    • Started: 25
    • Replies: 28
    • Total: 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

    #18141

    tomallan
    Keymaster
    • Started: 0
    • Replies: 417
    • Total: 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

    #18142

    sjhc1177
    Participant
    • Started: 25
    • Replies: 28
    • Total: 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

Viewing 3 posts - 1 through 3 (of 3 total)

The forum ‘Power Pivot’ is closed to new topics and replies.