Forum Discussion
Load and combine same structure .txt files from folder
Hi BA_Pete ,
It is not what I was expected, but your solution works.
And I have to admit that it is better to do your way.
I’m still in "Excel sheet" logical, manipulating data on multiple columns, one for each variable (T1,H1,T2,H2,T3,H3…), whereas thinking with "stack" data and "take" those I need in graph/chart/matrix_table with just putting the right context. Thanks for your reply. That will help me better than you think.
To come back to the initial issue in PowerQuery, I managed to get the table I want (even if it is useless). As a beginner, it took me about 20 steps (14 steps after automatic powerquery combining file) to accomplish the task ! That’s time consumming. I put the steps below "in case of" :
To sum up :
- after PowerQuery automatic combining jobs from folder (6 steps)
- I "group" by the field "n°" (primary key for each file. I could do it with "Date" field)
- Create one list by variable (Date, T, H)
- Extract value from list with separator "tab" for each variable
- Then split column by separator "tab" for each variable
- Then delete unwanted column
I’m sure that there is a quicker way to procede.
Thanks for your help.
Glad it's worked for you, happy to help.
Regarding whether there's a quicker way to restructure the data as per your initial request, there probably is but, as you've already mentioned, there's actually no point. You should really only be using Power Query to get your data into the optimal format for VertiPaq compression and SSAS handling, which your initial example data actually is.
Here's a recent thread where I go into a bit more detail on this:
Pete