Converting Text to Date

Power BI Data Model
21

#“Added Conditional Column” = Table.AddColumn(#“Renamed Columns”, “Billing Date”, each if [Month] = “Jan” then #date(2019, 1, 1) else if [Month] = “Feb” then #date(2019, 2, 1) else if [Month] = “Mar” then #date(3019, 3, 1) else if [Month] = “Apr” then #date(2019, 4, 1) else if [Month] = “May” then #date(2019, 5, 1) else if [Month] = “Jun” then #date(2019, 6, 1) else if [Month] = “Jul” then #date(2019, 7, 1) else if [Month] = “Aug” then #date(2019, 8, 1) else if [Month] = “Sep” then #date(2019, 9, 1) else if [Month] = “Oct” then #date(2019, 10, 1) else if [Month] = “Dec” then #date(2019, 12, 1) else if [Month] = “Nov” then #date(2019, 11, 1) else null, type date)

22

Please just replace #“Added Conditional Column” line… Ah, it must work now if you copy/paste above line that i sent. I got same error. Issue is when i use double quotes " instead of 2 single quotes ‘’ :slight_smile:

23

@AoakeP, @hafizsultan,

In thinking more about this, I believe the lack of a Year field in the original data may have led us to take a less than optimal initial approach. If you simply add a year field to the original Excel data, everything now works much more smoothly and simply in Power Query, and seems like a more sustainable way to manage the data in the future when 2020 and beyond records are added.

let
    Source = Excel.Workbook(File.Contents("C:\Users\brjul\Documents\Power BI Coding\Enterprise DNA Files\Hours Worked.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Division", type text}, {"Month", type text}, {"Hours", Int64.Type}, {"Year", Int64.Type}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Month", "Month - Copy"),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Duplicated Column", "Year", "Year - Copy"),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Duplicated Column1", {{"Year - Copy", type text}}, "en-US"),{"Month - Copy", "Year - Copy"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    #"Renamed Columns" = Table.RenameColumns(#"Merged Columns",{{"Merged", "Date Billed"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date Billed", type date}})
in
    #"Changed Type1"

Revised solution file and Excel file attached. See what you think.

24

Hi Brian and @hafizsultan

I appreciate the time you have both taken to help me with my issue.

I have gone back to the excel file and changed the structure of the origianl data to include a year column, so when i pull the file into the query editor is looks like this, now all is missing is the column that displays the date.

Do i add in the code that was sent through to me by Hafiz, or will the code now change because a year column has been added

image
image.png718×565 26 KB

Thanks guys
Paula

25

Hi @AoakeP,

Excellent solution provided by @BrianJ. You don’t need to write any code now. Just follow steps from your query. From your table, do following:

  1. Right click on Month and click “Duplicate Column”. It will create “Month-Copy” column.
  2. Similarly right click on Year and click “Duplicate Column”. It will create “Year-Copy” column.
  3. Select both new column, right click and select “Merge column”. After that rename it whatever you like.
  4. Change data type from text to date and it will become date.

image
image.png1074×559 49.5 KB

Hopefully, it will solve your problem :slight_smile:

1 Like
26

Wow it worked im so happy and cant thank you both enough for your time and persistance in helping me @sam.mckay both these guys are fantastic!

image
image.png1014×642 50.2 KB

1 Like
27

@AoakeP, @hafizsultan,

Thanks, glad that ultimately worked for you. Good team effort here, but I feel I could’ve made life easier for you by prompting for your file location first. @melissa has a cool way of doing this that I will need to learn for future problems like this. I am hesitantly including her here, just because I can only imagine the look on her face when she sees it took us 26 posts to get to this point…:crazy_face:

  • Brian
1 Like
28

We got there in the end @BrianJ with lots of learnings along the way which I’m sure other readers of the forum will get the benefit of :grinning:

1 Like
29

@AoakeP,

I like your “much was learned by all” attitude, so hopefully we can all just agree to ignore that the whole problem could have been solved by one line of DAX…:zipper_mouth_face:

DAX Date = CONCATENATE( ‘Hours Worked’[Month]&" ", ‘Hours Worked’[Year] )

  • Brian
30

@AoakeP,

Glad that it worked for you. @BrianJ ,provided solution and I was just trying with you to implement it. Thanks @BrianJ for your continuous support for all issues in this forum.