Dynamically split one table into multiple tables - Query Editor

[Anonymous]

My data comes in as text files and is structured as follows (Excel data example at this dropbox link: https://www.dropbox.com/s/nittio4xwnfd61x/Data%20Example.xlsx?dl=0😞

 

There's essentially 3 "data tables" within this one text file. Each one starts at the Header Line. The Header Lines would come in at different rows based on the text file. How can I dynamically split these "data tables" within this one file? There is a specific ID number on the second header line that I need to tag to each "data table" so that's why I need a way to seprate or isolate each one.

I know how to extract the ID number. I was thinking to create an index column and filter between header rows or something like that, but wanted to see if there was an approach that I wasn't thinking of that would be cleaner.

[Greg_Deckler]

Ideally, what are you expecting as output from this? 3 columns, 3 tables or ?

[Anonymous]

@Greg_Deckler  Thanks for the reply. So I'm importing a number of similar text files from SharePoint. My ideal output is one big table with all the data and one column that represents ID number. 

My guess is that the transformation would happen at the "Transform Sample File" step in the SharePoint "Helper Queries" and then when invoking the function to combine all the text files from SharePoint, it would combine all those into one table.

[Greg_Deckler]

Wait, a single table with a single column like:

ID 1
ID 2
ID 3

?

[Anonymous]

@Greg_Deckler In my example the first data grouping has about 25 rows of data. So the end result would have 25 rows with that data and a column with that associated ID number. The next grouping in my example has 7 rows of data. So below the 25 rows explained above would be the 7 rows of data with the different ID number in that ID column. And so on.

That's my ultimate goal. I can work on getting there, but I believe my first step would be to somehow split this one table into it's different data groupings. Not sure if there's a way to go from my data to end result easily, but I think if I can at least have the data split into different groupings then I can re-evaluate how best to get to my end result.

[v-lionel-msft]

Hi @Anonymous ,

It's better to give us a complete example data and make an output table in EXCEL, so that we can know your data structure and the results you want.

In addition, if it cannot be implemented in Edit Queries, do you mind using DAX?

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sytze]

Hi @v-lionel-msft 

I have a similar question. The only difference is that I dont want to output multiple tables, but just one. So let me give you a complete example. I have one xlsx file with financial transaction data for multiple companies like this. The first attached image is the exact format as I get the excel file and the next one is the how I want it. 

I hope that this will help @Anonymous as well as from this solution he should be able to use the split function to create the separate tables he requires.

And this is what is should look like afterwards:

Best regards

[Yuvarajand]

Hey,

Probably you found a way to achieve this. 

Do you recall your best approach?

Thanks,

Yuvi