Gear Up for Black Friday Sale at P3 Adaptive

November 20, 2015 at 10:46 am

Avi, going through your excellent ebook now. The chapter on Power Query is great. That said, the M code snippet on page 219 isn’t working as published in the ebook and it appears the accompanying book files aren’t available online yet. While troubleshooting the errors I think the “date” reference in the 7th step (“ChangeType1”) should also be highlighted in blue. Even with that fix, I’m only getting errors as values for the date, month, and year columns. Any suggestions?

November 21, 2015 at 5:03 pm

Hi Dean –

I ran into this same problem! I got mine to work, and here’s my code. Note that my table is called “SalesTable”, and my column is “Order Date”. Here are the changes that you need to make:

1) Change line 7 (beginning with “RawDatesTable = “)

2) Change line 8 as well (beginning with “ChangedType1 = “)

3) Lines 9, 10, and 11 need adjustments as well. However, this time you change it to [ColumnName], but no quotes. You’re referring to the column at this point, not the name.

let
ChangedType = Table.TransformColumnTypes(SalesTable,{{“Order Date”, type date}}),
MaxDate = Record.Field(Table.Max(ChangedType, “Order Date”),”Order Date”),
MinDate = Record.Field(Table.Min(ChangedType, “Order Date”),”Order Date”),
DaysElapsed = Number.From(MaxDate-MinDate),
DatesList = List.Dates(MinDate, DaysElapsed+1,Duration.From(1)),
RawDatesTable = Table.FromList(DatesList, Splitter.SplitByNothing(), {“Order Date”}, null, ExtraValues.Error),
ChangedType1 = Table.TransformColumnTypes(RawDatesTable,{{“Order Date”, type date}}),
InsertedDay = Table.AddColumn(ChangedType1, “DayNum”, each Date.Day([Order Date]), type number),
InsertedMonth = Table.AddColumn(InsertedDay, “MonthNum”, each Date.Month([Order Date]), type number),
InsertedYear = Table.AddColumn(InsertedMonth, “Year”, each Date.Year([Order Date]), type number),
in
InsertedYear

Note that you’ll likely need to backspace the code to get each single line of code onto 1 line (it doesn’t seem to like the code wrapping onto the next line).

Hope this helps!

~ Chris

Avi and Rob, thanks for such an amazing book!

November 23, 2015 at 12:41 am

Dean, you can download the PowerQuery code here PowerQuery_06_Calendar.PowerQuery.txt

BTW: The “Date” reference in 7th step does not need to be changed. I double checked that. Once you have the code and step through it it would become apparent.
We’re working hard to provide the download files as soon as we can. Maybe as soon as Monday 11/23. They’ll be made available at https://ppvt.pro/bookfiles

November 23, 2015 at 11:49 am

Thanks, Chris and Avi. Just what I needed.

November 26, 2015 at 7:53 am

Waiting for the blackfriday code… Have you sent it already.

November 26, 2015 at 9:43 am

Hi Avi so far I don’t get any discount code and Black Friday Sale will be starting from tomorrow.

November 26, 2015 at 10:18 pm

Raisur, the wait is over! Check out our latest blog post for all the information on our Thanksgiving sale https://powerpivotpro.com/2015/11/happy-thanksgiving/

November 29, 2015 at 9:29 am

Thanks for the info Avi. Today I have purchased the Powerpivot course. But I have got 1st Edition of the book not your new release “Power Pivot and Power BI” book. Can you please look into that matter.

November 29, 2015 at 4:06 pm

Hello Raisur, Thanks for purchasing our course. The book should now be available for download via our learning portal. If you login again via https://powerpivotpro.digitalchalk.com/ the Course Elements 1-3 have been updated to download the 2nd Edition (the new book).

Gear Up for Black Friday Sale at P3 Adaptive

Cancel reply