Forum Discussion

mmarcantonio1's avatar
mmarcantonio1
Regular Visitor
8 years ago

Move different data set from

Hi Community,

 

I have a set of files (same file type just pulled over different dates) where they all have the same format, with one data set (search queries with results) two blank rows, and another data set (search queries without results)- see screenshot for example.

 

 

Each file has a varying amount of rows for each data set, so the location of the two blank rows that separates each data set varies across these files.

Is there a way I can separate these datasets into different columns using PowerQuery (either via the UI or with M)? I want to automate separating these data sets so I can have the data in separate tables.

 

For now I plan to manually make these separate CSV files but I would prefer to automate this process.

 

Any advice would be much appreciated!

 

Thank you

 

 

3 Replies

  • ImkeF's avatar
    ImkeF
    Community Champion
    8 years ago

    Sound achievable, but I don't know how you except your result to look like. Please share a sample/pic.

  • mmarcantonio1's avatar
    mmarcantonio1
    Regular Visitor
    8 years ago

    Having thought this through a bit further I hope my sample result data would allow me to see the data visualized in two ways:

     

    1. as a line chart for searches with results and without results over time for basic trend performance.

    2. As a pivot table to review individual queries to understand if they had counts as either with results, without results, or both depending on the time period.

     

    I am not sure if the separated columns approach I mentioned for queries with results vs those without results (option on the right in the screenshot below) or if having the queries in the same column with a single column to distinguish queries that do have results from those with no results will be possible.

     

    Any insight into this would be much appreciated!

     

    Thank you.