Combing TXT files with different columns

[PowerBI123456]

Hi - I am trying to combine several TXT files that have different columns, so not all the columns are being captured. I found a few ways of doing it, but that assumes the header is already established. My problem is that I have to promote the headers after I combine. Any tips please? 

[TheoC]

Hi @PowerBI123456 

In Power Query, you can use Merge to bring them altogether.  Basically, when you have one or more columns that you want to bring together, you can merge them.  

Let me know if you want me to provide further steps.

Theo 🙂

[PowerBI123456]

Yes, please! How do you do that exactly? Thank you so much?

[TheoC]

Hi @PowerBI123456 

Here is a basic tutorial: https://support.microsoft.com/en-us/office/merge-queries-power-query-fd157620-5470-4c0f-b132-7ca2616...

Go to the "Perform a Merge Operation" section.

Hopefully this helps.

Theo 🙂

[PowerBI123456]

@TheoC  Thanks! but this requires me bringing in one file at a time right? The folder of files I am bringing will have a new file each week. 

[TheoC]

@PowerBI123456 sorry, just realised that what you're after is to use Folder as Source.  You won't need to use merge.  Similar to your situation, the example I provided in the earlier post with 15,000+ CSVs, each CSV has up to 480 columns and can have 5 rows of data to 500,000, generated every 15 mins.  Although your scenario may not use that many files / records, etc., it still applies the same logic and will work perfectly.

Give it a go and let me know if you need any further help k!

Theo

[PowerBI123456]

Hi - thank you so much for your quick responses and help! However, I am not following. How are you capturing all the different columns from a folder? Would you be able to provide an example? My problem is also that I have to promote the headers after combining since they are txt files and the first row is coming as "column 1", "column 2", etc. instead of the actual headers. 

[TheoC]

@PowerBI123456 each TXT should be structured in some format that has a delimeter, correct?  For example, this might be a pipe | a comma , semicolon ; or something that differentiates what is and what isn't a new column?  Basically, when you select Folder as Source, you can Combine and Edit.  During this process, you select to do your manipulation / transformation on a Sample File or the First File.  Herein, whatever you do to the First File or Sample File, Power BI applies the same transformations to all of you TXTs in the folder.

[PowerBI123456]

I can try recreating the situation with dummy data if that helps. Appreciate your help!

[TheoC]

@PowerBI123456 that would be great if you can.  In the meantime, please watch this video: https://www.youtube.com/watch?v=JAw4rrpc7vk  it will hopefully provide you with the necessary steps to follow and apply to manage the various columns.

[PowerBI123456]

Yup, I got that part. But the issue is that the first  file might have 70 different columns, but the second file has 80. What is happening is those 10 extra columns from the second file are not coming over and there is mismatch since the extra columns are in the middle of the file. 

[TheoC]

@PowerBI123456 no, not at all. Just dump the new file into the folder.  Below is a screenshot of a folder I use as source for a specific client.  Each file has different number of columns, some the same and some different.  They're monthly management reports and I generate it, dump it in the folder used as source then move on to the next.  

Another client report I've built generates CSV files every 15 mins from an FTP Server and saves them down to a specific location. There are 15,000+ files in this example and over 8GB of data. CSV are pretty much identical to TXTs in their format for Power BI. 

Hope this helps and provides context 🙂