Forum Discussion

wwpmoa's avatar
wwpmoa
Copper Contributor
Jun 25, 2024

Working with PowerQuery and Automate Script to split and group columns with mixed, not uniform data

Dropbox Link The timesheet .csv from vendor requires a lot of manipulation because the rows and columns contain mixed, not uniform data. Can Power Query transform it from source data ("Timesheet") to desired flat format ("Report")? I experimented with PQ. It helped some but did not get all of it.  Looking for best method or combination of methods. I do not have Copilot yet in Excel. I do not know or have Python. I can do Power Query or VBA or Script.  Could the Query be saved as a Script to use weekly? 

  • wwpmoa 

    Here is the Power Query part.

    - first query only loads the timesheet with minimal preparations; second query which reference it does transformation.

    In the sample csv is taken from local file. You need to change on your location. If that's single file on SharePoint it's better to use From Web connector, much faster. Take file URL at the bottom of Details pane in SharePoint (not from browser address bar) or use From SharePoint Folder connector if you have several timesheets.

    Transformation is bit hardcoded and probably not accurate - not sure I understood timesheet logic correctly. Plus didn't add empty columns which are not in csv.

    Thus consider as mock-up.

  • wwpmoa 

    In general that's job for Power Query, but all depends on concrete file. It will be better if you may share share the sample. You may put it on OneDrive, Google Drive, whatever, share with everyone and post the link here.

    Office Script works with the objects within the same file, and so far it refreshes data connections only to the sources within the file (ore exactly to OData as well). Since within Excel you work with external csv that's not an option.

    One of alternatives could be dataflow, but that's if you have Power BI Pro or higher subscription.

      • wwpmoa 

        Here is the Power Query part.

        - first query only loads the timesheet with minimal preparations; second query which reference it does transformation.

        In the sample csv is taken from local file. You need to change on your location. If that's single file on SharePoint it's better to use From Web connector, much faster. Take file URL at the bottom of Details pane in SharePoint (not from browser address bar) or use From SharePoint Folder connector if you have several timesheets.

        Transformation is bit hardcoded and probably not accurate - not sure I understood timesheet logic correctly. Plus didn't add empty columns which are not in csv.

        Thus consider as mock-up.

Resources