Share via

Changing Data with Power Query

Anonymous
2025-05-17T18:28:43+00:00

I am trying to convert the data from the top table into the second data through either power query or manipulating a pivot table.

We collect timesheet information from our field staff through a Microsoft form. They populate activities with start and stop times, with multiple activities in one day.

Each line represents one day, and there are columns for activities and start/stop times.

We want to convert the activity data to a vertical representation and duplicate the employee names and dates so that we can manipulate the data easier.

How can I do this? Should I be collecting the information differently? I don't want my employees to enter a new form for each activity, I would rather they continue to enter on a daily basis.

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2025-05-18T09:23:53+00:00

If your want solution only by PQ then try this M code. Table name is Table1.

let

Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

Unpivoted = Table.UnpivotOtherColumns(Source, {"Date", "Employee", "ID"}, "Attribute", "Value"),

Parsed = Table.AddColumn(Unpivoted, "ActivityNum", each 

    Text.PadStart(Text.Select([Attribute], {"0".."9"}), 2, "0"), type text),

Parsed2 = Table.AddColumn(Parsed, "FieldType", each 

    if Text.Contains([Attribute], "start", Comparer.OrdinalIgnoreCase) then "Start Time"

    else if Text.Contains([Attribute], "stop", Comparer.OrdinalIgnoreCase) then "Stop Time"

    else "Activity", type text),

RemovedAttr = Table.RemoveColumns(Parsed2, {"Attribute"}),

Pivoted = Table.Pivot(RemovedAttr, List.Distinct(RemovedAttr[FieldType]), "FieldType", "Value"),

Cleaned = Table.RemoveColumns(Pivoted, {"ActivityNum"}),

Filtered = Table.SelectRows(Cleaned, each [Activity] <> null and [Start Time] <> null and [Stop Time] <> null),

Final = Table.SelectColumns(Filtered, {"Date", "Employee", "Activity", "Start Time", "Stop Time"}),

Sorted = Table.Sort(Final, {{"Date", Order.Ascending}})

in

Sorted

Was this answer helpful?

2 people found this answer helpful.
0 comments

13 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2025-05-18T07:13:36+00:00

    Hi,

    If you want solution by formula, then try this one:

    =LET(ts, TEXTSPLIT(TEXTJOIN("",, REPT(BYROW(B3:.C11, ARRAYTOTEXT) & ";", ROWS(D3:.L11))), ", ", ";", TRUE), hs, HSTACK(IF(ISNUMBER(--ts), --ts, ts), TRANSPOSE(WRAPCOLS(TOCOL(D3:.L11), 3))), VSTACK({"Date","Employee","Activity","Start Time","Stop Time"}, FILTER(hs, IFERROR(BYROW(TAKE(hs,, -3), LAMBDA(r, NOT(AND(r = 0)))), FALSE))))

    It is a dynamic formula. Format cells A13:A20 as date and cells D13:E20 as time.

    Hope this helps.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2025-05-17T18:46:37+00:00

    Dear Firee99, 

    Thank you for reaching out to us regarding your request to transform your timesheet data for easier manipulation. We understand that consolidating the data from your Microsoft Form submissions into a more accessible vertical format would significantly enhance your workflow. 

    Below are the detailed steps to achieve this using Power Query: 

    Method 1: Using Power Query (Recommended) 

    This method will help you transform your data from a horizontal format into a vertical one, making it easier to manage and analyze. 

    Load Data into Power Query: 

    • Open your Excel file containing the data.
    • Highlight the data range (including headers) → go to the Data tab → click From Table/Range.
    • Power Query Editor will open with your data.

    Unpivot the Data: 

    • In the Power Query Editor, select all the Activity, Start Time, and End Time columns.
    • Go to the Transform tab → click Unpivot Columns → Unpivot Selected Columns.
    • Your data will now be converted into a vertical format.

    Clean Up the Data: 

    • Remove the Attribute column if it's not needed.
    • Rename the columns to Activity, Start Time, and End Time for clarity.

    Load Data Back to Excel: 

    • Click Close & Load → the transformed data will appear in a new sheet, ready for analysis.

    Method 2: Using Pivot Table 

    If you would like to try another method, you can use a Pivot Table. However, it is slightly more complex and requires more manual adjustments. If you prefer, I can guide you through this as well. 

    Improving Data Collection: 

    To further streamline your process, I would recommend updating your Microsoft Form to collect activities in a structured table format. This way: 

    • Each activity is listed on a separate row, even if there are multiple per day.
    • When the form is submitted, the data flows directly into Excel or SharePoint, allowing for real-time updates.
    • Power Query can be set to refresh automatically, displaying all activities in the desired format without manual intervention.

    If you require more in-depth guidance or support for these steps, you may also contact Microsoft’s higher-level technical support through:     

    Our specialized support team will assist you step by step and ensure you get the most appropriate solution for your needs.    

    I hope the information I provided earlier was helpful to you. If you have any additional details or updates regarding this issue, feel free to share. Your feedback is greatly appreciated. If you found my answer helpful, marking it as an answer would be helpful for others facing similar situations. 

    Best regards,     

    Dustin-TP- MSFT | Microsoft Community Support Specialist

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  3. Anonymous
    2025-05-17T22:51:07+00:00

    Excel 365 Pro Plus with Power Pivot and Power Query.

    With massive fictitious database, in your format.

    With Pivot Chart and Slicer.

    With Grouping.

    As suggested by Dustin: Improve your data collection.

    https://www.mediafire.com/file_premium/8esem7dkwbqdsit/05_17_25.xlsx/file

    https://www.mediafire.com/file_premium/k19c9oitqt7l5xj/05_17_25.pdf/file

    Was this answer helpful?

    0 comments
  4. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more