Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have imported the data from Google Analytics connector and I'd like to add a new coloumn which will have values like, "Jan, Feb, Mar etc" from my [Month of Year] column which is in following format: YYYYMM.
I have tried following solution, but to no avail:
1) Tried converting the [Month of Year] column into Data/Time, earlier it was Text, it won't allow me to convert into Date or Date/Time and convert the data into "Error"
2) Custom Column:
Used following snippet
Snippet: =Date.FromText(Text.Range([Month of Year],0,4)&Text.Range([Month of Year],4,2))
Ref link: https://community.powerbi.com/t5/Desktop/How-to-convert-text-20150714-to-date/m-p/92515#M39064
None of the above solution has helped me, please recommend how do I go about solving this issue.
Regards
Solved! Go to Solution.
Hi @sumonigupta,
In your scenario, you can follow the steps to convert YYYYMM to your desired format.
1. Right click your table and select “New column”.
2. Create the new column using the formula below.
Column = SWITCH(LEFT(RIGHT('Table1'[Month of Year],4),2), "01","Jan", "02","Feb","03","Mar","04","April", "05","May","06","Jun","07","Jul","08","Aug","09","Sep","10","Oct","11","Nov","12","Dec")
Thanks,
Lydia Zhang
Hi @sumonigupta,
In your scenario, you can follow the steps to convert YYYYMM to your desired format.
1. Right click your table and select “New column”.
2. Create the new column using the formula below.
Column = SWITCH(LEFT(RIGHT('Table1'[Month of Year],4),2), "01","Jan", "02","Feb","03","Mar","04","April", "05","May","06","Jun","07","Jul","08","Aug","09","Sep","10","Oct","11","Nov","12","Dec")
Thanks,
Lydia Zhang
Assuming [Month of Year] is Text, try this:
= Table.AddColumn(Source, "Short Month", each Text.Start(Date.MonthName(Date.FromText([Month of Year]&"01")),3))
Not an expert in Power BI, so I am assuming, I need to add a new column and execute this snippet?
I did and it gives "Table" as the value in each row, which when clicked open ups a new table with four column and 1 row!
Or do I have to execute it differently?
Please recommend
From your first post, it looks like you've already used the Advanced Editor in Power Query to add a Custom Column, using the code snippet you reference. Just replace that line in the query with my suggested changes and see how you go.
If you still have problems, can you post your whole query, so I can show you exactly what to change?
Cheers,
Steve.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.