Forum Discussion
Load and combine same structure .txt files from folder
Hello,
I need to load and combine multiple .txt files from a folder to PowerBI, and have a table with side by side column.
Data are separated by tab.
These .txt files have got the same 4 columns structure, with these headers :
"n°" "Date" " Temp" "Hyg"
Data from "n°" and "Date" are the same for each file.
When I ask PowerQuery to combine theses files, it appends them one above the other :
| file.name | n° | Date | Temp | Hyg |
| file1.text | 1 | 01/01/2023 | 25 | 50 |
| file1.text | 2 | 01/02/2023 | 30 | 50 |
| file2.text | 1 | 01/01/2023 | 20 | 40 |
| file2.text | 2 | 01/02/2023 | 35 | 55 |
What I would like :
| n° | Date | file1.Temp | file1.Hyg | file2.Temp | file2.Hyg |
| 1 | 01/01/2023 | 25 | 50 | 20 | 40 |
| 2 | 01/02/2023 | 30 | 50 | 35 | 55 |
Thanks for your help
3 Replies
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
