Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
Solved! Go to Solution.
Hi @Atiroocky ,
Try using the Matrix visual:
Both [_temp] and [_hyg] measure are MAX(Table[Temp/Hyg]).
Pete
Proud to be a Datanaut!
Hi @Atiroocky ,
Try using the Matrix visual:
Both [_temp] and [_hyg] measure are MAX(Table[Temp/Hyg]).
Pete
Proud to be a Datanaut!
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 :
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
Proud to be a Datanaut!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
63 | |
56 | |
27 | |
17 | |
13 |