convert MMM to MMM/YY from Excel to PowerBI

[PatrickWong]

I am trying to import data from excel (company exchnage rate) to powerbi. The excel cell for month is DD/MM/YYYY but is formated to MMM

When imported to powerBi, the cell is still MMM,

Need help to get the cell to mm/yyyy or dd/mm/yyyy. Eventually the year row will be deleted and the month will be promote to header row.

[v-jingzhang]

Hi @PatrickWong 

I did a test based on your description. The month values in the Excel have m/d/yyyy values, but they have a custom format "mmm". Just like below. (Notice: my system setting is using US format "m/d/yyyy". Your date values will be consistent with your system setting e.g. "dd/mm/yyyy". )

After connecting in Power Query Editor, I get the following table. The month values are converted to number values like 42370, 42401... These are date values in number format. If you convert a date to Number in Excel, you will see the same result. 

Based on above result, you can

1. Remove the top one row (Year row);

2. Promote first row (Month row) as headers, remove the auto-generated "Changed Type" step after "Promoted Headers" step;

3. Select the first column and unpivot other columns;

4. Change the "Attribute" column to Whole Number type, then change it to Date type. This can convert this column to Date successfully. If you convert it from Text type to Date type directly, you will have Errors. 

5. Then select "Attribute" column and pivot the column. Select "Value" column for Values column with "Don't Aggregate". 

You will get the result you want. 

However, I recommend that you do not do the last Pivoting column step. Remain the table in the result of Step 4 will be more friendly for further calculation and reporting in Power BI Desktop if you will go on to create a report on this data. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[PatrickWong]

Thanks for guide, It still not solving my problem. I think it is a setting issue which I dont know how to correct it

If I use power query within the excelsheet where the exchange rate is, I am able to dates on the month row, but when I use a new excel datasheet and power query, I get text in the date row. When I use powerbi to get the data for the exchange rate, I am also getting the text in the date row.

Dont know how to attached file here but here is the link for both the excel and pbix

https://1drv.ms/u/s!Ap3wVydjwtruyS7vGxxxEuJWGDFj?e=aBRCkI

[v-jingzhang]

Hi @PatrickWong 

Sorry for the late reply. I checked the Excel file you provided. For months earlier than or equal to Nov-2022, the months actually have a date value. However, for the hidden Dec-2022, it only has "Dec" as the value. 

When I use Power BI to get this Excelworkbook, I can get those months as numbers except for the last "Dec". Can you modify the last "Dec" to a date value in Excel, then use Power Query to connect to it again?

I can always see those month values in number format no matter I use Power Query in Excel or in Power BI Desktop. 

Best Regards,

Jing

[PhilipTreacy]

Hi @PatrickWong 

If it's just a case of formatting then you can change the way the date is displayed by selecting the column containing the data, and choosing the desired formatting from the Formatting section of the Ribbon.

However if your dates are actually stored as text, it's a bit more complicated.

Can you please provide sample data.

regards

Phil