Power Query - Change US Date to UK Date and Pivot without aggregating

Today we started our journey into mastering Power Query. We did a couple of Preppin' Data Challenges in Power Query, including 2023 week 4. I came across two challenges which I had to solve differently than what I was used to with Tableau Prep.

TRANSFORM DATE

The first issue I encountered happened when I wanted to transform a column with an American date format into a date. Power Query had recognised it as a string so I tried to just change the format to a date. This caused quite a few rows to show error messages. Of course, because an American date is in MMMM/dd/yyyy format but my Power Query only recognises dd/MMMM/yyyy formats.

I tried a few different ways, which felt way too complicated until I found an easier way. TRANSFORM USING LOCALE.

Right click on the column you want to transform, click on Change Type, choose Using Locale and set the Data Type to Date and the Locale to English (United States).

This tells Power Query that you want to change the data type of that column into a date and that the format the date is in is American. Power Query now knows that it has to read it differently and can change that to the format of your settings.

Pivot Errors

The second issue I faced was when I pivoted Demographics data. In Power Query a Pivot is always a rows to columns change. Meaning we want the values in the different rows of a specific column to turn into Headers. The values of the other selected column will then populate the corresponding rows.

When I did this the first time around with the Preppin' Data 2023 week 4 dataset, this happened:

Something wasn't right. What had it done to my values. I decided to go back to my pivot step and take a closer look. And there it is: in the advanced options you can define whether the values get aggregated or not. In my case I did not want them to get aggregated but to be displayed as they were for the rows, so I chose don't aggregate and it worked.

I hope these tips are handy for when you come across different dates and pivoting in Power Query.