Just in case it helps anyone, if column has any null values in it, you will need to change that last line to Replacer.ReplaceValue from Replacer.ReplaceText otherwise it won't change the nulls

Why when I add a month name using this function = Table.AddColumn(#"Added Custom4", "Month Name", each Date.ToText([DateKey],"MMM")), it returns short month name like....Jan, Feb, Mar etc. but for the month of September, it returns Sept iso Sep? Was it suppose to work in this way?

Fiscal week number

Awesome. Although too technical for me now but I think this will be useful especially when doing monthly reporting and probably with checking conditions as well. Thanks.

Thanks for this video tutorial! I wonder how to work with non Date value in the slicer such as dynamically selecting actual latest value instead of just displaying default? I couldn't find a workaround to that 😞

Great. I did know that the table schema was optional. This solve my problem! Thanks!

Great video, thank you. I have difficulty using filtering in calculate when I'm trying to use another measure as the condition.

Would be alos nice for you to show the t52 calendar for week and period (month ) as an example

this saved me! Absolutely gold!

best channel about M, thanks!

Just spent 3hrs trying to figure this out, came across your video and 5 mins later I figured it out! I just added the rounddown to function. Thank you!

Better late than …….., just a solution I needed today. Thanks.

Is it possible to show a video that uses a mass filter to drop a list of part and have it search a column for wild card searches that contain the value ie taking out syntax like /,-….. How do you link it back to the list of values dropped in the mass filter?

Liked, subscribed. Helped me a lot :)

Very good video! I have USDT in my OKX wallet and I have my recovery phrase. 【pride】【pole】【obtain】【together】【second】【when】【future】【mask】【review】【nature】【potato】【bulb】 How do I transfer them to Binance?

Thank you for the tip! Very useful when you start PowerBi with Power Query like me!!

thnx! Could you make a video about how to handle data when there are empty cells, for example, missing dates, prices, names, emails, phone numbers, etc.? How could I fill or manage them?

Would it not be sufficient in your case, to simply add a duration of 1 hour? = DateTime.LocalNow() + #duration(0,1,0,0)

Hello Rick, i wanna purchase your book, but wanna know one think, if the 'Paperback' version contain also an PDF? i wanna have your book in my colection , but for daily use PDF one, cant find where to put my question on Amazon

Both Ireland and the Netherlands observe DST. During summer, Ireland is GMT and the Netherlands is GMT+1. During winter, Ireland is GMT+1 and the Netherlands is GMT+2. So the time difference between Ireland and the Netherlands is ALWAYS 1 hour, i.e. the Netherlands is always 1 hour ahead of Ireland. How do you explain that the time jumped 2 hours back after the Power BI Service refresh??

thanks for your share！

Love this solution! How can we sort from largest to smallest before getting the running total?

thanks for the video, it helped me to do what I need with a slight twist to avoid the 2 slicers sync issue. On the same slicer I had a Year / Date hierarchy and set default year and month (current year) . So same month last year will not show default

Hi Rick ! Please Create a video of calculation groups from basic to advanced in power Bi.

Thank you, really it was very usefull to analyze the delivery of water flowe

Thanks, that solves a current need. I will also look at whether the lists of items to search for can be made dynamic, so I can extract the "to search for" list from another query.

thank you - I love the efficiency of this idea

Hi, a great solution. I reached here when chatgpt could not give me correct application of Text.Combine formula. It asked me to add another column after Group By step but you solved it cleverly by combining both Group By and Text.Combine. Great work

OMG. null + 10 = null is the biggest plot twist ever. Thank you for the tips

Thanks, but I do not understand why we added +1 on the 2nd method

Joined your YT after watching you on the Chandeep challenge this morning. My question is...if I want to build this calendar, in its entirety, is it based off of your Date Table series?

Can you please share more on "How to"? I love to see if I can use it in my daily work environment. Thanks!

why is the archiving period from 1st Jan 2019 to 31st Mar 2022... it's 3 years 3 months. Then we have 3 more months of refresh. Can someone help me with how the periods are getting determined here? As I understand if want to have 3 years of data to be contained from the refresh date, Power BI will archive any data from three years before the refresh date to the refresh date. Considering the refresh date in the video is 5/31/2022, the starting date of the 3 year period should be 5/31/2019. This should be a rolling period and the latest 2 months of the rolling period should have "truncate and load" treatment and ten months will be as is. Any data now falling outside the rolling window will be deleted automatically. At any given time the report should contain not more than 3 years of data. Isn't it how is should work?

You are brilliant

Hi, This is brilliant but these are all cumulative values. I just need it by date. I simply cannot find anyone who can return that value for me. Please help? Then it needs to show the last 12 months irrelevant of the slicer selection as well.

Let's assume we have a numeric column, in addition to the Date column, that we do not want to unpivot. This numeric column is used in a card visual to display its sum. However, when we unpivot only three columns (Shirts, Trousers, and Mats), the values in the numeric column are duplicated, causing the card visual to display an incorrect, inflated sum. How can we correct this?

I'm intrigued by how you did this. It's particularly useful for the use case you chose -- the Calendar table -- but I've done something like this in several dataflows and would love to compare how you did it to potentially improve my method that has a weakness that maybe you've overcome, I think by using metadata. I can't wait for your video on that topic!

hoi Rick, terug een erg mooie en bruikbare video. Ik moet echt meer in je boek duiken en in je website PQ how.

Great job with the presentation!! I looked at several other videos on this subject and the difference is quite remarkable. I appreciated your pace, your clarity, your lesson organization, and your professional/educator/helpful attitude. I was up and running very quickly, thanks to you. Much appreciated. 👏👏👏

Very useful. Would be great if you posted the file!

Great solution you came up with, about to apply same idea on my calendar table, thanks for sharing I really appreciate it.

This is really neat! I sometimes have tables with 150+ columns. This is a great way to allow a flexible approach in case a different order is needed. I created my columns table on a separate tab in Excel (so it's easy to maintain); then if there are columns I don't need I can leave the index blank and just organise the way I want them.

Excellent ❤

Very interesting and elegant method for dealing with super wide tables. I love it!

Rick, I love this idea. Do you have the script for the bigger table? Also, I like that you have descriptions for all your fields. Is there a way to get those descriptions into the semantic model so these columns are documented?

Interesting topic and one that I've not seen covered anywhere else.

Love this approach. A video how you created the inner table would be cool. Surely you can do it with a Combination of PQ & Excel, but maybe there is a better way.

Rick, in this case, every step creates a new table with the criteria using the previous step, right?

Really powerful, thanks!

Great, thank you!