Power Query 3 Letter Month
Sharyn Requena
I am trying to create a target table. On my table, we have a 3 letter initial for the month (period) that contains target qouta for the month. PowerBI treats this column as text instead of Date. I am wondering what do i need to do to covert this field to date. I receive an error when I convert it using Power BI.
Good day
I am looking for a solution for the following:
Using power query I have created additional columns from a date field in the source table. Is it possible to shorten the Month name for eg January = Jan and Day name eg. Monday = Mon ?
I have to show the first 3 letters of the months in order for my table to fit properly. I do not need years to be visible.
I already have a date hierarchy [FirstOfMonth], and created a 3 letter abbreviation calculated column [3LetterMonth]
There are many posts about three-letter month abbreviations but none about one-letter month abbreviations (J F M A M J J A S O N D). I would like to create a chart with one-letter month abbreviations on the x-axis and have it sort in chronological (not alphabetical) order across multiple years, and yet I have been unable to find anything online about doing this, which surprises me because I would think this would be a commonplace occurrence. I can do it in Excel with ease in just a few clicks. How can I do it in Power BI desktop, including using DAX code to get there? I would appreciate any help from the community. Thank you.
If you need the one-letter abbreviations to span multiple years in some way, or correspond to dates rather than months, we could come up with a way of encoding that information, possibly using a few different invisible characters.
Because of the duplicate initial letters, one method is to create a calculated column (either DAX or Power Query) that includes the desired initial letter of the month, followed by zero-width blank characters (such as Unicode character 8203), repeated a different number of times for each month. I used the month number (1-12) as the number of repetitions.
Thank you, OwenAuger. I admire your intellect for coming up with that approach, which would not have occurred to me. It gets me a good part of the way there, but the challenge I'm now having is that I can't use the one-letter abbreviations on the x-axis of a chart, since PBI Desktop still treats them like categories to be summed instead of as a time series. I tried sorting the new Month Initial column by the Date column, but it wouldn't work owing to the duplicates in the Month Initial column (which would also be the case if three-letter month abbreviations were used over more than one year, as I've found separately). Ultimately my need for the one-month letter abbreviations is so that they can be used on several very small charts (hence the need for such abbreviated abbreviations). Any ideas about how to address the charting issue? And thank you again for your reply!!
Hello @OwenAuger
You're completly right! I was after tough day with lots of hours at the desk, therefore I've tried to make a measure. First thing this morning was doing calculated column and it works brilliantly! Thank you for your input in the community!
The example solution and image you have in this post are exactly what we looking for, but I can't seem to find the file that you reference. Can you possibly display or send me the example file solution?
OwenAuger, thank you once more. Your chart is indeed what I am trying to get to, albeit in a more compact form. My results are improving as they are now in chronological order; now if I can just get Power BI Desktop to stop adding both Januarys, both Februarys, etc., (I have two years's worth of data) to make it a true time series across the two years, I will be there. Yours is doing it correctly, so the key must be there in your pbix file. I have downloaded it and am studying it closely to understand what else I should do to mirror your result. Thank you!
Is there an excel function for converting a three letter month abbreviation (such as FEB, APR) to a number? I've been using a mapping table and then using v-lookup from there, but curious if there's a function or faster way.
Steps as per above illustrations: Fire up Power BI Desktop > edit query > Select your date table > select the date column > Add Column tab > Date button > Month > Month (again) > Select the Home tab > Closed and Apply.
There are many ways to build a date table in Power BI Desktop, I will not cover all these in this post. My preference, mostly because it is quicker, is to create it in Power Query. However, for the purpose of this illustration, I will do it in a way most people are familiar with, using DAX (Data Analysis Expressions). This is how:
I need to sort by month as you explained in the article I got it to work. My problem is when the year changes. That is, my dates start from 1st June 2016 and now the current data date is Feb 2017. So when the data is displayed in a bar chart it goes as Jan, June, July, Aug, Sep, Oct, Nov, Dec.
What I need is to display as 2016 month first and the 2017- meaning Jun, Jul, Aug, Sep, Oct, Nov, Dec, Jan, Feb.
I stumbled across your post and hoping you may be able to help me... I have the following date scheme in my data - 1130915. This is the century (don't ask), 2 digit year, two digit month, and two digit date. I need to reference the year and month to identify the fiscal year and then want that fiscal year to populate in a new column. Our fiscal year runs from June to May, so I need dates with 6-12 of 2013 and 1-5 of 2014 to say fiscal 14. Looking at this post, I get the jist of referencing the year or the year, but how do I reference them both together with a range???
Kevin, is the column showing as blank, or does it actually show a value of null? If it shows as blank, then try selecting the column, do a replace - selecting to replace with null, but leave the first parameter blank. That should convert your blanks to null, which should allow the formula to work.
Kelly, let's start by getting your date into a real date format. From there you should be able to spin off the logic you need. To do that, assuming your first column is called "myDates", you could use the following formula:
The date values were already converted; if there are no date values in a cell, it is represented as null. Apologies for the semantics.
What worked in the end was to replace all Errors with the value Missed. Not sure if this is the way to do it but it sees to work.
Ken - Thanks very much for this info - exactly what I needed to convert from excel to Power BI - I have a question about the nested syntax you added in the comments - if test1 then if test2 then value-if-test2-is-true else value-if-test2-is-false else value-if-test1-is-false - I think that there needs to be VALUE-IF-TEST1-IS-TRUE ELSE after the first then. Correct? otherwise there is no option for test1 to be chosen.
I am trying to use this to group numerical data into 4 categories
? 300 days old
I can get it into 3 - if [#"Pty Age (workdays)"]300 then "biggest" else "medium"
but for 4 I need to use an AND and I can't figure out how to do that in DAX. Should I be using this or is there another function that would work better.
Thanks
As far as popping up an box to dynamically collect it... I don't think so. For that, I'd probably cook up some VBA to determine if the file exists, write the appropriate value back to a worksheet cell, then pull that in dynamically (with a parameter table) to drive the file to load.
Your suggestion to break into logical rows (if/else if/else) and add brackets did the trick. I just couldn't work some logic out but now it is all working.
Thanks for the tip :-). Do you have any recommendations on good documentation for implementing dates/numbers into PQ (or the new Excel 2016 flavor).
M is for Data Monkey has a full table of date functions and their Excel equivalents in Chapter 24. With regards to numbers, it depends on what you're looking for. If you're talking about dealing with conversions (to/from numbers/text/dates, etc...) these are in Chapter 17 (pgs 143-144).
I want to create a normal Pivot, which I can do with lots of manipulation, using only the detail lines, inserting 2 columns to show Foods and (in the case above) "Cereals". Can Powerquery help in this instance?
So if I read this correctly, you want to split the data into three columns: Category, SubCategory and Product, so that you can then use them in separate columns on the Pivot. Providing I got that right, then yes. Right click the column in Power Query --> Split Column --> By Delimiter. Enter 4 spaces, then ensure that "repeatedly" is selected. That should be it.
Thanks for this. Only discovered Power Query today. Also tried normal If but luckily got your article. I have very limited knowledge of VB. It seens as if Query uses VB codes and formulas? For example, the text.range does not ring any bells from a normal Excel formula background?
No, not even close, to be honest. Power Query uses a language called M which is VERY different from standard Excel formulas, Power Pivot's DAX formulas and VBA. To be honest, one of the best things you could do to get the most of power query is pick up a copy of my book, as that covers how to use the tool in depth.
Hi Ken,
Hoping for a bit of guidance please. I am trying to round Lat & Long values to 2dp and then combine them to give a sudo primary key. The end result should look something like: "48.86,-2.30". My concern is that the numbers are being rounded and where there is a zero it is being omitted (eg. "48.86,-2.3").
I created the following bit of code using an IF statement to examine the string and append the zero if necessary but it isn't working. Can you advise me please?
I would like to ask question regarding the example in chapter 18. After going through all steps and adding custom column "employee" name of employee is not showing instead the "error" is showing. I followed the step mentioned in your book. I am using excel 2016.
4a15465005