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.
Hi All,
I am just thinking through if there is any other way to convert the data below in to a date.
Source Data | Jan | Feb | Jan |
Source Data | FY19 | FY19 | FY20 |
Target | Jan-2019 | Feb-2019 | Jan-2020 |
Basically i need this data type to drag it to filter field and play around the data.
Thanks in advance,
Srinivasan Vishwanathan
Solved! Go to Solution.
Hi Fowmy,
For some reason i cannot share the screen shot here.
But yes its the same way you have it up there.
I need a result column to have (Jan-20 & Jan-19).
Thanks for your help.
@vishwanathans
If you need a dax solution, go to your table in the model and under Home Tab, click on New Column and the following code, make sure you rename table and column names as per your table:
Month Year = DATEVALUE("01-" & Period[month] & "-"& RIGHT(Period[year],2))
If you want to do it in Power Query then
Go to Power Query, create a new blank query and paste the below code on the advanced editor and check the step:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMU9JRcos0MlCK1YlWcktNAnMNLZViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [month = _t, year = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"month", type text}, {"year", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Date.From("01-" & [month] & "-" & Text.End([year],2) )),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type date}})
in
#"Changed Type1"
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@vishwanathans
Can you reshare the sample data in a format that you have in your table
Is it like:
month | year |
Jan | FY20 |
Feb | FY19 |
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hi Fowmy,
For some reason i cannot share the screen shot here.
But yes its the same way you have it up there.
I need a result column to have (Jan-20 & Jan-19).
Thanks for your help.
@vishwanathans
If you need a dax solution, go to your table in the model and under Home Tab, click on New Column and the following code, make sure you rename table and column names as per your table:
Month Year = DATEVALUE("01-" & Period[month] & "-"& RIGHT(Period[year],2))
If you want to do it in Power Query then
Go to Power Query, create a new blank query and paste the below code on the advanced editor and check the step:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMU9JRcos0MlCK1YlWcktNAnMNLZViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [month = _t, year = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"month", type text}, {"year", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Date.From("01-" & [month] & "-" & Text.End([year],2) )),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type date}})
in
#"Changed Type1"
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Thanks Fowmy it worked thank you.
Would it be possible to help me on this too. I know its much of a ask i am sorry.
Below is my data. This how the system output was.
I need to prep my data to this. I there any way that i can do it here.
@vishwanathans
Glad it worked for you!,
Regarding the additional question, please create a new question and provide your sample source data in text format (not image), solutions are possible.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
I would say create a Calendar Table and relate it to your 'Source Data'.
Proud to be a Super User!
Hi Chris,
can you please help me with some link for reference. I am pretty new here.
Thanks for your help.
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.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |