Need some help with some Row Level Query Transformation

[DavidNealon]

Hi All,

I’ve put together a testing spreadsheet and want to set it up so I can create some Dashboards. I’ve already transformed my table once to bring my rows as columns and I’m pretty happy with the column headings.

But I’m not sure how to move my Comment Fields for each Test to be a column next to each Test.

Hoping someone could help me here. I’ve put together a sample Spreadsheet. with an example of what I’ve got (as a Before Tab in the spreadsheet) to at the moment and (an After Tab in the spreadsheet) of the End result I’m hoping to be able to transform my data into so its usable.

Sample Data File for Query Transformation.xlsx (13.6 KB)

Thanks in advance for any help you can provide.

Dave

[Mohammed_ali]

Good Day David,

Check this out.

Move one row up in a particular column

[Melissa]

Hi @DavidNealon,

Think the easiest way to perform that transformation is to split the query up and merge them back together. Let’s explore that process… This is what your source looks like

And this is the desired lay-out.

1. We don’t need the first column (A) from the Source, so deleted that.
2. Added an Index from 0
3. Added a Custom Column to update the Index with this logic:
   if Number.Mod([Index], 2) =0 then [Index] else null
   
   
4. Filtered out all the null values in the Custom column

= = = Create a Duplicate Query, and work on that from here on = = =

5. Renamed the new query ‘Comment’

6. In the Applied Steps pane, select the previous step (Added Custom1) and modify the logic:
   if Number.Mod([Index], 2) =1 then [Index]-1 else null
   

   

7. Selected ‘Merge Queries’ => Merge Queries as New
   

   

8. Expanded the Comment Columns

9. Demoted the Headers to the First Row

10. Transposed the Table
    

    

11. Added a ‘Sort’ column by Extracting Text.AfterDelimiter from Column1

12. Replace Values so that null becomes 0

13. Sorted the Table AND Deleted the Sort helper column

14. Transposed the Table back

15. Promoted Headers

16. Cleaned up Columns with this result.
    

    

I hope this was helpful. Here’s my sample file.
eDNA - Query Transformation.pbix (75.0 KB)

Added an alternative solution.

[EnterpriseDNA]

Hi @DavidNealon, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!

[DavidNealon]

Awesome thanks Melissa

I learned something new here