PowerQuery Excel - how to convert a schedule created in excel and convert to PowerBI dashboard

[nicoleclancy91]

Hello PowerBI Community!

I need help converting this excel spreadsheet that my collegue uses to schedule their employees, through power query into a table that will allow me to analyze the following data points:

1. Number/Count of Work Orders (WO)

2. Use the WO # as the unique identifier

3. Use the dates to help me allocate projections based on contract budgets (on a different spreadsheet already into the dashboard)

   3a. This has been our biggest issue so far since the dates are established at each column header, and the WO number is within the cell below with other information. I have tried to transpose the data and it still doesnt give me accurate data in power query. 

4. The employee name and information doesnt necessarily matter, we just need to understand how many people are scheduled for that work order so we can see the projections and variance remaining. 

Our goal is to have the WO#, Date and Count of WO# as well as count of employees per WO to connect with our budget analysis in a table visual on the powerBI dashboard. 

The current spreadsheet my collegue uses to schedule is as shown below without sensitive information. Any ideas or solutions are greatly appreciated! 

[nicoleclancy91]

Thank you for your rapid response! 

Since we are using the confidental data, we are trying to do the steps manually and were stuck on this step:

#"Transform List" = List.Transform(#"Table To Columns", each List.Split(_, 2)),

Where can we transform the list? We were able to get all the data to here:

Since we are not able to just copy and paste the dataset into our query since it has different data, were not able to use it the same. My co-worker and I would greatly appreciate the manual steps to help us, this would save us tremendously!

thank you!!

[jgeddes · Accepted solution]

Ok. I will walk through the steps.
Since you mentioned you are using an Excel file I transfered my dataset to Excel. The source line now becomes

Source = Excel.Workbook(File.Contents("C:\YourFilePath\YourFileName.xlsx"), null, true),

Assuming your data is in sheet1 the next line will need to be

Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],

You should now have something that looks like...

This routine depends on the columns being type text so we need to add 

#"Type Change" = Table.TransformColumnTypes(Sheet1_Sheet, List.Transform(Table.ColumnNames(Sheet1_Sheet), each {_, type text})),

Note: This step was not in the original response and is written this way to allow the column count to increase or decrease dynamically in your source file.

You can now promote the headers 

#"Promoted Headers1" = Table.PromoteHeaders(#"Type Change", [PromoteAllScalars=true]),

If you use the UI to promote the headers it adds a change type row automatically. Delete that change type row step if it appears.

We now need to add a blank row since the eventual transformation is going to group the rows in two row groups and we need a row to "group" with the header row.

#"Insert Row" = Table.InsertRows(#"Promoted Headers1", 0,{Record.FromList(List.Repeat({""},Table.ColumnCount(#"Promoted Headers1")),Table.ColumnNames(#"Promoted Headers1"))}),

Now to demote the header row (Use Headers as First Row)

#"Demoted Headers" = Table.DemoteHeaders(#"Insert Row"),

Again, if a change type step automatically appears here, delete it.

Now we are going to create a list of columns from the table.

#"Table To Columns" = Table.ToColumns(#"Demoted Headers"),

Now, from the list that we created we need to split the list into groups of 2.

#"Transform List" = List.Transform(#"Table To Columns", each List.Split(_, 2)),

And now combine the list pairs into a single cells within the list, seperated by a space.

#"Transform List2" = List.Transform(#"Transform List", each List.Transform(_, (pair)=>Text.Combine(pair, " "))),

Turn that list back into a table

#"Table from Columns" = Table.FromColumns(#"Transform List2"),

You should now have a table that resembles...

Promote the first row as header (delete the automatic change type step if it shows up)

#"Promoted Headers" = Table.PromoteHeaders(#"Table from Columns", [PromoteAllScalars=true]),

Now select Column1 and "Unpivot Other Columns" (Doing it this way will allow the column count to increase or decrease without having to amend the code)

#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"RWIC "}, "Date", "Value"),

Select the "Date" column and Parse the date

#"Parsed Date" = Table.TransformColumns(#"Unpivoted Other Columns",{{"Date", each Date.From(DateTimeZone.From(_)), type date}}),

Now select the "Value" column and Split by Delimiter ("-")

#"Split Column by Delimiter" = Table.SplitColumn(#"Parsed Date", "Value", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Client", "Project", "Work Order", "Location"}),

Now we select the "Location" column and Split by Delimiter - (space " ")

#"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Location", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Location", "Project Number"}),

Add a Custom Column that looks for PTO in the Client column

#"Added Custom" = Table.AddColumn(#"Split Column by Delimiter1", "isPTO", each if Text.Contains(Text.Upper([Client]), "PTO") then "PTO" else null),

Repeat this step for "Off" values

#"Added Custom1" = Table.AddColumn(#"Added Custom", "isOFF", each if Text.Contains(Text.Upper([Client]), "OFF") then "OFF" else null),

 Now we will replace the PTO values from the Client column with null

#"Replaced Value" = Table.ReplaceValue(#"Added Custom1","PTO",null,Replacer.ReplaceValue,{"Client"}),

And the Off values from the Client column with null

#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","OFF",null,Replacer.ReplaceValue,{"Client"})

You should now have...

Hopefully this helps get your data transformed. 
If you are still having concerns, feel free to private message me and I would be happy to help from there.

Cheers.

[nicoleclancy91]

thank you so much! this helped tremendously!

[jgeddes]

Power Query can fix this for you.

I created a dataset to mimic your spreadsheet...

Did the following steps...

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kvMTVVQ0DC1sDDTjMnzL8lILVLwzEvLV9JRcs7JTM0rUXBU0FUIKMrPSk0uUTAEssP9FQyNjE1MzcwtgDyf/OTEksz8PAVH7DqcIDrAyi2NjZB1OJFhR6xOtFJERIQuFEcAjQApBFIgtUDKzMjEEKwKxWNFo14b9dqg8Zo55TktIMSfqo7F6VDK48DfzQ1K4rVeKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [RWIC = _t, #"Tue 03/01/22" = _t, #"Wed 03/02/22" = _t, #"Thu 03/03/22" = _t]),
#"Insert Row" = Table.InsertRows(Source, 0,{Record.FromList(List.Repeat({""},Table.ColumnCount(Source)),Table.ColumnNames(Source))}),
#"Demoted Headers" = Table.DemoteHeaders(#"Insert Row"),
#"Table To Columns" = Table.ToColumns(#"Demoted Headers"),
#"Transform List" = List.Transform(#"Table To Columns", each List.Split(_, 2)),
#"Transform List2" = List.Transform(#"Transform List", each List.Transform(_, (pair)=>Text.Combine(pair, " "))),
#"Table from Columns" = Table.FromColumns(#"Transform List2"),
#"Promoted Headers" = Table.PromoteHeaders(#"Table from Columns", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"RWIC ", type text}, {"Tue 03/01/22 ", type text}, {"Wed 03/02/22 ", type text}, {"Thu 03/03/22 ", type text}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"RWIC "}, "Date", "Value"),
#"Parsed Date" = Table.TransformColumns(#"Unpivoted Other Columns",{{"Date", each Date.From(DateTimeZone.From(_)), type date}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Parsed Date", "Value", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Client", "Project", "Work Order", "Location"}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Location", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Location", "Project Number"}),
#"Added Custom" = Table.AddColumn(#"Split Column by Delimiter1", "isPTO", each if Text.Contains(Text.Upper([Client]), "PTO") then "PTO" else null),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "isOFF", each if Text.Contains(Text.Upper([Client]), "OFF") then "OFF" else null),
#"Replaced Value" = Table.ReplaceValue(#"Added Custom1","PTO ",null,Replacer.ReplaceValue,{"Client"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","OFF ",null,Replacer.ReplaceValue,{"Client"})
in
#"Replaced Value1"

 And ended up with...

You can use this table to create the measures you need.

Hope this gets you pointed in the right direction.