Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Atiroocky
Frequent Visitor

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.text101/01/2023 2550
file1.text201/02/2023 3050
file2.text101/01/2023 2040
file2.text201/02/2023 3555

 

What I would like :

 


Date
file1.Temp
 file1.Hyg
  file2.Temp
 file2.Hyg
101/01/2023 25 502040
201/02/2023 30 503555

 

Thanks for your help

 

 

 

1 ACCEPTED SOLUTION
BA_Pete
Super User
Super User

Hi @Atiroocky ,

 

Try using the Matrix visual:

BA_Pete_0-1692885405154.png

 

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

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

3 REPLIES 3
BA_Pete
Super User
Super User

Hi @Atiroocky ,

 

Try using the Matrix visual:

BA_Pete_0-1692885405154.png

 

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

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

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 :

  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

 

Atiroocky_0-1692945381967.png

 

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:

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

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.