Load and combine same structure .txt files from folder

[Atiroocky]

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

[BA_Pete · Accepted solution]

Hi @Atiroocky ,

Try using the Matrix visual:

Both [_temp] and [_hyg] measure are MAX(Table[Temp/Hyg]).

Pete

[Atiroocky]

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 :

1. after PowerQuery automatic combining jobs from folder (6 steps)
2. I "group" by the field "n°" (primary key for each file. I could do it with "Date" field)
3. Create one list by variable (Date, T, H)
4. Extract value from list with separator "tab" for each variable
5. Then split column by separator "tab" for each variable
6. Then delete unwanted column

I’m sure that there is a quicker way to procede.

Thanks for your help.

[BA_Pete]

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:

https://community.fabric.microsoft.com/t5/Power-Query/Laptop-is-running-very-slow-with-not-very-soph... 

Pete