Converting Text to Date

AoakeP · February 16, 2020, 11:44pm

Hi i have a fact table that originates froma table in excel, there are 3 columns in the table Division, Month and Hours. In my data model i want to be able to filter on the Division and the Month for total hours worked.

I have a calculation using sumx to calculate the total hours worked from the hours column.
“2019 Total Hours Worked = SUMX(‘Emp Hours 2019’,‘Emp Hours 2019’[Hours])”

I am able to filter using a slicer to to filter the total hours worked by division but i am unable to use the a Date slicer to filter by month.

I think the issue is that my Month column in the fact table is in Text format e.g “Jan”, "Feb etc so the filter i am using in the data model to connect the date table to the Month column in the fact table does not work.

Is there a way i can convert the Month column in my fact table to a date format, or do i need to add a calculated column to add a date column

Help please, thanks Paula

BrianJ · February 17, 2020, 12:53am

@AoakeP.

You could do this as a DAX calculated column, but I recommend instead doing it in Power Query. Because you didn’t have a Year column, this code is a bit “brute force”, but it works. If you have years in your data in addition to 2019 though, we will need to modify this to take that into account.

To use this, you will need to modify the source in the first Power Query step to point to your Excel table, since I just brought the data in via screen grab OCR:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdA9C8IwEAbg/5K5Q3K5fI2iOBTEwbF0qCWIUNpS26H/3nyog7nllof37nibhh2nYfD9+pzGF6tY3Y1hOi2NZm31r2d/j6rAGkIv3RLVcCUIPcxRhbAcORneY9giV4TWW36LWyB1SKud1pa6vD1i2AkpCb35OR02gvr62q9xNYDgVPjk+8SI9svLPC3d6n9dhqzSheUmBWjlCss9hhyWuU+LEgGI3J7MYGm5wLgz/Nm+AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Division = _t, Month = _t, Hours = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Division", type text}, {"Month", type text}, {"Hours", Int64.Type}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "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)
in
    #"Added Conditional Column"

Once you’ve done this, you can connect the relationship from your date table to your new date created, and then slice your data based on month:

I hope this is helpful. Full solution file attached.

Brian
eDNA Forum - Text to Date Solution.pbix (263.2 KB)

AoakeP · February 17, 2020, 1:25am

Thanks Brian

Where do i modify the source do i copy this into the advance editor?

Here is what my source currently looks like in the query editor.

Also i will be adding in hours for 2020

AoakeP · February 17, 2020, 1:42am

i have completed made a mess of the data, not sure how to fix it. Help

hafizsultan · February 17, 2020, 1:46am

Hi @AoakeP,

Can you please put clear snapshot as i can not view error. Also, as mentioned by Brian, you can make changes using power query. You will have to go to Advanced editor to make those changes suggested by Brian.

AoakeP · February 17, 2020, 1:48am

Hi

Hopefully this screen shot is a lot clearer.

Do i add this code onto the code that is allready in the advance editor?

hafizsultan · February 17, 2020, 1:52am

Hi @AoakeP AoakeP,

Yes, you will have to add this code to existing code. What you can do is to click “Emp Hours 2019” table and then click “Advanced Editor”. Then you can send me snapshot and i can modify code for you.

AoakeP · February 17, 2020, 2:00am

Thanks

Here is the Advance Editor code

let
Source = Excel.Workbook(File.Contents(“C:\Users\AoakeP\OneDrive - Envirowaste Services Limited\Power Bi\Health Safety Reports\Hours Report 2019 PBI.xlsx”), null, true),
Table1_Table = Source{[Item=“Table1”,Kind=“Table”]}[Data],
#“Changed Type” = Table.TransformColumnTypes(Table1_Table,{{“Division”, type text}, {“Jan”, type number}, {“Feb”, Int64.Type}, {“Mar”, type number}, {“Apr”, type number}, {“May”, type number}, {“Jun”, type number}, {“Jul”, Int64.Type}, {“Aug”, type number}, {“Sep”, type number}, {“Oct”, Int64.Type}, {“Nov”, Int64.Type}, {“Dec”, type number}}),
#“Unpivoted Other Columns” = Table.UnpivotOtherColumns(#“Changed Type”, {“Division”}, “Attribute”, “Value”),
#“Renamed Columns” = Table.RenameColumns(#“Unpivoted Other Columns”,{{“Attribute”, “Month”}, {“Value”, “Hours”}}),
#“Changed Type1” = Table.TransformColumnTypes(#“Renamed Columns”,{{“Hours”, Int64.Type}})
in
#“Changed Type1”

hafizsultan · February 17, 2020, 2:05am

let
Source = Excel.Workbook(File.Contents(“C:\Users\AoakeP\OneDrive - Envirowaste Services Limited\Power Bi\Health Safety Reports\Hours Report 2019 PBI.xlsx”), null, true),
Table1_Table = Source{[Item=“Table1”,Kind=“Table”]}[Data],
#“Changed Type” = Table.TransformColumnTypes(Source,{{“Division”, type text}, {“Month”, type text}, {“Hours”, Int64.Type}}),
#“Added Conditional Column” = Table.AddColumn(#“Changed Type”, “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),
#“Unpivoted Other Columns” = Table.UnpivotOtherColumns(#“Added Conditional Column” , {“Division”}, “Attribute”, “Value”),
#“Renamed Columns” = Table.RenameColumns(#“Unpivoted Other Columns”,{{“Attribute”, “Month”}, {“Value”, “Hours”}}),
#“Changed Type1” = Table.TransformColumnTypes(#“Renamed Columns”,{{“Hours”, Int64.Type}})
in
#“Changed Type1”

hafizsultan · February 17, 2020, 2:05am

Give this a try

hafizsultan · February 17, 2020, 2:09am

let
Source = Excel.Workbook(File.Contents(“C:\Users\AoakeP\OneDrive - Envirowaste Services Limited\Power Bi\Health Safety Reports\Hours Report 2019 PBI.xlsx”), null, true),
Table1_Table = Source{[Item=“Table1”,Kind=“Table”]}[Data],

#“Added Conditional Column” = Table.AddColumn(Source, “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),
#“Unpivoted Other Columns” = Table.UnpivotOtherColumns(#“Added Conditional Column” , {“Division”}, “Attribute”, “Value”),
#“Renamed Columns” = Table.RenameColumns(#“Unpivoted Other Columns”,{{“Attribute”, “Month”}, {“Value”, “Hours”}}),
#“Changed Type1” = Table.TransformColumnTypes(#“Renamed Columns”,{{“Hours”, Int64.Type}})
in
#“Changed Type1”

AoakeP · February 17, 2020, 2:13am

[quote=“hafizsultan, post:11, topic:5496”]
et
Source = Excel.Workbook(File.Contents(“C:\Users\AoakeP\OneDrive - Envirowaste Services Limited\Power Bi\Health Safety Reports\Hours Report 2019 PBI.xlsx”), null, true),
Table1_Table = Source{[Item=“Table1”,Kind=“Table”]}[Data],

#“Added Conditional Column” = Table.AddColumn(Source, “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),
#“Unpivoted Other Columns” = Table.UnpivotOtherColumns(#“Added Conditional Column” , {“Division”}, “Attribute”, “Value”),
#“Renamed Columns” = Table.RenameColumns(#“Unpivoted Other Columns”,{{“Attribute”, “Month”}, {“Value”, “Hours”}}),
#“Changed Type1” = Table.TransformColumnTypes(#“Renamed Columns”,{{“Hours”, Int64.Type}})
in
#“Changed Type1”

Great thanks do i copy this into the advance editor at the source step?

AoakeP · February 17, 2020, 2:15am

Hi

I tried copying the code at the Source step and tried again at the last change step in the query editor both times it failed. see screen shot below. can you pin point what i am doing wrong?

hafizsultan · February 17, 2020, 2:24am

Can you please attach your excel file for that particular query? There is some syntax error. It should be simple as it is working with me.

AoakeP · February 17, 2020, 2:26am

Hi

Here is my a snapshot of my excel file

Division	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
Collections	96876.00	95287.00	97051.00	118040.00	98405.00	96082.00	119668.00	99133.00	98711.00	122103.00	100196.00	124483.00
Corporate	12256.00	12659.00	12246.00	13422.00	13742.00	12243.00	14325.00	13114.00	12330.00	14258.00	12188.00	13531.00
Post Collections	42775.00	41797.00	42804.00	51356.00	43287.00	42616.00	53720.00	44782.00	43397.00	53446.00	44081.00	56721.00
Technical Services	7909.00	7664.00	8242.00	9464.00	8520.00	8097.00	9776.00	8413.00	8407.00	10170.00	8413.00	9681.00
Owner Drivers	4620.00	4620.00	4620.00	4620.00	4620.00	4620.00	4620.00	4620.00	4620.00	4620.00	4620.00	4620.00
Contractors	5652.55	5912.00	2030.55	5018.60	5429.70	4207.10	4356.00	1675.50	3294.50	6589.00	5879.00	811.70

AoakeP · February 17, 2020, 2:26am

Once i have the table in the query editor i unpivot from the Division column

hafizsultan · February 17, 2020, 2:55am

Hi @AoakeP,

I have simulated your scenario and it worked for me. What you need to do is simple. Do all transformations first and then open “Advanced editor” and remove everything on/after “in” put this code below.
,
#“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)

in
#“Added Conditional Column”

hafizsultan · February 17, 2020, 2:56am

let
Source = Excel.Workbook(File.Contents(“C:\Users\hzn\Desktop\Learning\DAX\test pq.xlsx”), null, true),
Table1_Table = Source{[Item=“Table1”,Kind=“Table”]}[Data],
#“Changed Type” = Table.TransformColumnTypes(Table1_Table,{{“Division”, type text}, {“Jan”, Int64.Type}, {“Feb”, Int64.Type}, {“Mar”, Int64.Type}, {“Apr”, Int64.Type}, {“May”, Int64.Type}, {“Jun”, Int64.Type}, {“Jul”, Int64.Type}, {“Aug”, Int64.Type}, {“Sep”, Int64.Type}, {“Oct”, Int64.Type}, {“Nov”, Int64.Type}, {“Dec”, Int64.Type}}),
#“Unpivoted Columns” = Table.UnpivotOtherColumns(#“Changed Type”, {“Division”}, “Attribute”, “Value”),
#“Renamed Columns” = Table.RenameColumns(#“Unpivoted Columns”,{{“Attribute”, “Month”}}),
#“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)

in
#“Added Conditional Column”

Above is my working code with your example without any error:

and I get following sample table.

AoakeP · February 17, 2020, 3:30am

Gee im not ahving much lick and im hoping its just something really silly that im missing…here is my code in the advanced editor after removing the in and text after the in i copied the additional code into the editor again i got an error

let
Source = Excel.Workbook(File.Contents(“C:\Users\AoakeP\OneDrive - Envirowaste Services Limited\Power Bi\Health Safety Reports\Hours Report 2019 PBI.xlsx”), null, true),
Table1_Table = Source{[Item=“Table1”,Kind=“Table”]}[Data],
#“Changed Type” = Table.TransformColumnTypes(Table1_Table,{{“Division”, type text}, {“Jan”, type number}, {“Feb”, Int64.Type}, {“Mar”, type number}, {“Apr”, type number}, {“May”, type number}, {“Jun”, type number}, {“Jul”, Int64.Type}, {“Aug”, type number}, {“Sep”, type number}, {“Oct”, Int64.Type}, {“Nov”, Int64.Type}, {“Dec”, type number}}),
#“Unpivoted Other Columns” = Table.UnpivotOtherColumns(#“Changed Type”, {“Division”}, “Attribute”, “Value”),
#“Renamed Columns” = Table.RenameColumns(#“Unpivoted Other Columns”,{{“Attribute”, “Month”}, {“Value”, “Hours”}})
,
#“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)

AoakeP · February 17, 2020, 3:39am

The error seems where the code starts there is a red line under the word “#Added” when i hover over the error it reads “token identifier expected”