Latest Enterprise DNA Initiatives

Pivot/Transpose Data and Single Row of Data

Team,
I’m working on a project where I need to track each person through each Step of the process…unfortunately the data comes with each persons name for each step. Each step may or may not have a date associated with it (which is fine).

Q. How do I not only Pivot/Transpose the Step Name so that each and then make sure that there’s only one row per person with all the associated Step Dates (Step Start Date & Step Completed Date) are in the associated Step Name column?

I’ve attached sample data and hope this helps.

Thanks,
DDarenTestDS.xlsx (17.3 KB) DD_Test_DS.pbix (23.2 KB)

Hi @DDewbre,

Give this a go, just paste the code in a new blank query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZZdb5swFIb/ipXroIH5SMJdliKtXdMxyJaLqhceWGCFGImPdNmvn6kbkmETnIpdoRwpz2Mfjl/z/DxZfw0n00m4J1XKng95Stlji7Ls2JSjFMd1hsEdphXKgPcb7VnZ0OBcgzrUwcLVHeCvWc3WHF4yDNecN7WX6QA9CFYgxFFdkOrYQ7U0U+e1uWs6StR2zV4Q9lDFmjp1jWMSsVaEUYExJTT5Dw6/PEYp8A4oq1FFcjqeomm5X+QRLkvJ0s0Pc7+vwH3MZoS9SXCXR6UK2VQif/O9T/dPmw04dSd+4xjmedpk7VCDB6tN2QFbHDG7WLPgGuQ+YRyDR0STGiUYbHBZNSComWyNurEAsDkiy7VMOMj2MY3ZuwOfUbRLirymMQjwgeBXroAOVziuceqL6FXYwCvY5sWuEa0QE8aowoLAbgVd6/CsV6io1IC2EnC7/OkBLyMJ+ZVhaR5NJxcU9rcdLvgZeKgzgtjzUZoebf7oi3bOOrUbsD3xMa5Enh+jOGQBcobMPg6WJ8gZY0nQMzW0PEIgJ7EZm7u2bOGWYkeEDJGSxeIwui9GRsIPJclYm+iPknEMl1kyDrEbJmrZIX7PCEfullMsfHNdpZGMIfZlTv+wX8syzXcN8AuiFMkvOrjgONs1DY5j15x1ukCtbqeu8+WTylP8zWHA9oR1vMqOq+OqQfge99C1jdPd2vXfsJ+eof3Ho7eerlxZ1M7uMFi/CeznJWmyH/yg6IBIhvgki++4GaOXvw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Business Organization Name" = _t, #"Employee Last Name" = _t, #"Employee First Name" = _t, #"Employee Middle Name" = _t, #"Step Name" = _t, #"Step Start Date" = _t, #"Step Completed Date" = _t]),
    GroupRows = Table.Group(Source, {"Business Organization Name", "Employee Last Name", "Employee First Name", "Employee Middle Name"}, {{"AllRows", each _, type table [Business Organization Name=nullable text, Employee Last Name=nullable text, Employee First Name=nullable text, Employee Middle Name=nullable text, Step Name=nullable text, Step Start Date=nullable text, Step Completed Date=nullable text]}}),
    AddTemp = Table.AddColumn(GroupRows, "Temp", each 
        let
            DelCols = Table.RemoveColumns([AllRows],{"Business Organization Name", "Employee Last Name", "Employee First Name", "Employee Middle Name"}),
            Unpivot = Table.UnpivotOtherColumns(DelCols, {"Step Name"}, "Attribute", "Value"),
            Combine = Table.CombineColumns(Unpivot,{"Step Name", "Attribute"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Step"),
            Transpose = Table.Transpose(Combine),
            Promote = Table.PromoteHeaders(Transpose, [PromoteAllScalars=true])
        in 
            Promote
    ),
    RemoveColumns = Table.RemoveColumns(AddTemp,{"AllRows"}),
    ExpandTemp = Table.ExpandTableColumn(RemoveColumns, "Temp", {"Schedule Dental Exam Step Start Date", "Schedule Dental Exam Step Completed Date", "RRC Security Step Start Date", "RRC Security Step Completed Date", "Schedule ERS Step Start Date", "Schedule ERS Step Completed Date", "Schedule Medical Screening Step Start Date", "Schedule Medical Screening Step Completed Date", "Schedule Psych Evaluation Step Start Date", "Schedule Psych Evaluation Step Completed Date", "RRC Processing Step Start Date", "RRC Processing Step Completed Date", "QC Identity Docs Step Start Date", "QC Identity Docs Step Completed Date", "OPE/INTT Scheduled Step Start Date", "OPE/INTT Scheduled Step Completed Date", "RCTs Scheduled Step Start Date", "RCTs Scheduled Step Completed Date", "Need Language Test Step Start Date", "Need Language Test Step Completed Date", "Pending Background Review Step Start Date", "Pending Background Review Step Completed Date", "New Working Candidate Step Start Date", "New Working Candidate Step Completed Date", "Start Step Start Date", "Start Step Completed Date", "WAVE Eligible Step Start Date", "WAVE Eligible Step Completed Date"}, {"Schedule Dental Exam Step Start Date", "Schedule Dental Exam Step Completed Date", "RRC Security Step Start Date", "RRC Security Step Completed Date", "Schedule ERS Step Start Date", "Schedule ERS Step Completed Date", "Schedule Medical Screening Step Start Date", "Schedule Medical Screening Step Completed Date", "Schedule Psych Evaluation Step Start Date", "Schedule Psych Evaluation Step Completed Date", "RRC Processing Step Start Date", "RRC Processing Step Completed Date", "QC Identity Docs Step Start Date", "QC Identity Docs Step Completed Date", "OPE/INTT Scheduled Step Start Date", "OPE/INTT Scheduled Step Completed Date", "RCTs Scheduled Step Start Date", "RCTs Scheduled Step Completed Date", "Need Language Test Step Start Date", "Need Language Test Step Completed Date", "Pending Background Review Step Start Date", "Pending Background Review Step Completed Date", "New Working Candidate Step Start Date", "New Working Candidate Step Completed Date", "Start Step Start Date", "Start Step Completed Date", "WAVE Eligible Step Start Date", "WAVE Eligible Step Completed Date"})
in
    ExpandTemp 

.
I hope this is helpful

1 Like

Melissa
that didn’t seem to work. Plus, how did you do it as this is just a sample file the actual file has over 10k rows for only 650 people. I’ve attached screen shots of what I see.

Hi @DDewbre,

Okay, attached your sample PBIX
DD_Test_DS.pbix (50.5 KB)

You can rebuilt this using the UI, in a separate query and transferring the code once you are done.

Go to the Group By step, click off to the side in the white space and select “add as new query”

Select all columns that aren’t of intrest and remove them (alternatively select every thing of intrest and “remove other columns”)

With the first column selected, go to “unpivot other columns”

image

With the first 2 columns selected, choose merge columns

Transpose the table

Promote 1st row

Transfer the generated code to your query

.
I hope this is helpful

2 Likes

Melissa,
this was super duper helpful!! The part I’m stuck on is, of course, the very last step where I’m supposed to paste the code. So, I’m not sure where I should be pasting that and my query doesn’t look as neat as yours does. Which makes me think I’ve missed or didn’t communicate something properly.

So my original data pull is from SharePoint Online but I created a new query from that based on your steps above. Then I grouped by Business Operations Name…which maybe I should have grouped by Employee Last Name or does that matter?

Thanks
D

Transpose and Merge Code.txt (4.9 MB)

Hi @DDewbre,

I hope this illustrates the last step of the process more clearly.
Above the purple line the steps you’ve created in your spin off “Steps” query. Copy this section.

In your main query “Empolyee workflow steps” add a custom column give it a Name and enter a 0, press Okay. Next, open the Advanced Editor window and after the each statement, add the let statement, select the 0 and replace it with the code you’ve copied. Finally change the table reference as illustrated in yellow.

I hope this is helpful.

Hi @DDewbre, did the response provided by @Melissa help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

I’m still working through the process she has outlined. :slight_smile:

1 Like

Melissa,
I apologize for not quite getting it. I’ve attached the code and the view of where I started. It looks like there’s only one statement, but that doesn’t seem right with your statement. Am I supposed to add the code after each step in the query editor via the advanced editor?EmployeeWorkflowQueryText.txt (3.9 KB)

Hi @DDewbre,

This illustrates what you should be aiming for.
Understand that without an actual file I’m unable to provide further support.

image

I hope this is helpful.

Ok. Let me see if I can sanitize the file enough for you to work with it. I really appreciate you helping me out. I will learn how this is done so I can help others in my organization as well. :slight_smile:

Melissa,
I’ve attached a clean version that I’m hoping you will be able to use and I can learn the “How” afterwards. The column data I’m looking to make into columns is “Step Definition Name” column.DDTest2.pbix (382.3 KB)

Thanks
D

Hi @DDewbre,

I needed to extract the data and load that again because you didn’t provide a file.
You will have to check the Group By step, and possibly update that. I suspect there’s an issue due to anonomizing the data that occurred when filling down the ID column…

Employee Workflow Steps.csv (3.6 MB)
DDTest2.pbix (1.1 MB)

Well, don’t you know I fill a little dumb for not attaching the source file. I have added it here for you in case. I will try and follow the steps outlined as well and see if I can get it to work. I will let you know how it goes. :slight_smile:Employee Workflow Steps.xlsx (1.5 MB)

Thanks,
D

1 Like

Hi everyone. Bumping this post for more visibility.

Hi @DDwebre! We noticed that your inquiry was left unsolved for quite some time now. We strive to answer ALL inquiries in the forum. However, if you are sure that you provided all pertinent context to your concerns and read how to use the forum more effectively and still find your question unanswered, you can check out tutorials to learn this yourself as your membership also comes with relevant resources that may help you with your Power BI education.

While our users and experts do as much as reasonable to help you with your inquiries, not all concerns can be attended to especially if there are some learnings to be done. Thank you!

Sorry, I’ve been on travel. Work getting in the way of getting work done! LOL! I’m working on this and trying to implement.

Thanks for everyone’s patience!
D

Ok. I can’t get this to work for some reason…it’s just not working. What do you need from me to help me get this working? I’m happy to upload the source file again if needed etc.

Thanks in advance!
D

Hi @DDewbre,

If you are still having problems implementing the M code, watch this video. It covers the process of building out logic in a separate query and subsequently implementing that logic in the original query.

Roger! I shall check it out and hopefully it will help me out.:slight_smile: