Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a column with shortened months (Oct, Nov, Dec etc) but need to add another column with Year where only Oct, Nov, Dec are 2020 and all other months are 2021.
Then I need to add both and show as MM/YYYY or 01/MM/YYYY so I can have type as date for timeline dashboard
I'm new to Power BI so a little detail would be appreciated!
Solved! Go to Solution.
See this @perishkabb . I keyed in Oct-Jan and got this table from it:
The first formula is:
if List.Contains({"Oct", "Nov", "Dec"}, [Month]) then 2020 else 2021
The second formula is:
Date.FromText([Month] & " " & Text.From([Year]))
From there you can format your date however you like. Power BI has custom date formats like Excel does. Here is the full M code as an example:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k8uUYrViVbyyy8D0y6pyWDaKzFPKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Year", each if List.Contains({"Oct", "Nov", "Dec"}, [Month]) then 2020 else 2021),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each Date.FromText([Month] & " " & Text.From([Year])))
in
#"Added Custom1"
How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.
Note that the best practice here is to not use the date column I've given above, but to use a date table that is has a 1:many relationship with that date field, then use your Date Table to do your report dimensions. You can get/create date tables from many places, but I have an article here on how to do it in Power Query that will give you about 30 rich date fields for a variety of uses.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingExcellent @perishkabb . Glad I was able to help.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingSee this @perishkabb . I keyed in Oct-Jan and got this table from it:
The first formula is:
if List.Contains({"Oct", "Nov", "Dec"}, [Month]) then 2020 else 2021
The second formula is:
Date.FromText([Month] & " " & Text.From([Year]))
From there you can format your date however you like. Power BI has custom date formats like Excel does. Here is the full M code as an example:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k8uUYrViVbyyy8D0y6pyWDaKzFPKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Year", each if List.Contains({"Oct", "Nov", "Dec"}, [Month]) then 2020 else 2021),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each Date.FromText([Month] & " " & Text.From([Year])))
in
#"Added Custom1"
How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.
Note that the best practice here is to not use the date column I've given above, but to use a date table that is has a 1:many relationship with that date field, then use your Date Table to do your report dimensions. You can get/create date tables from many places, but I have an article here on how to do it in Power Query that will give you about 30 rich date fields for a variety of uses.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingIt worked! Thank you so much!! 🙂
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |