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
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.
Hi
Hopefully this screen shot is a lot clearer.
Do i add this code onto the code that is allready in the advance editor?
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.
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”
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”
Give this a try 
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”
[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?
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?
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.
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 |
Once i have the table in the query editor i unpivot from the Division column
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”
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.
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)
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”
#“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)
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 ‘’