Power Query | Combining binaries results in lost data from text file

[Cory Barnes]

This is only since the most recent Power Query update. I've attached a screenshot of what Power Query displays as well as an attachment of the raw data file being consolidated. Please advise.

[#2]

Hi @Cory Barnes,

I get the same issue as you whether I use the older version of Power Query or the new one.

Can you send a screenshot of the original M code from the Power Query Advanced editor for the Query that used to work OK

Thanks

[#3]

Just a follow up to my earlier response.

It looks like the text file is fixed width.  

I pulled it in using the following steps (See attached image and file)

Note: I didn't change the data type column in the last step as my system isn't set up for US format

[#4]

Hi Wyn,

Thanks for your response.  The challenge I am having is related to importing multiple text files (same structure and format as the previous attachment) and trying to subsequently consolidate them.  I do not have the original code from the previous version as my machine crashed recently (which is why I had to install the latest and greatest version of Power Query).  I've attached the previous documented steps I used previously, that of course are no longer working with the new version.  This should hopefully give some insight into the full process.  Although the overall flow is completely different, my primary challenge is the omission of data in the text file which of course drives the numbers off when consolidating the file.  Thanks for looking at this with me.

[#5]

Hi Cory

Can you send me a screenshot of the editor window with the queries expanded (see circled items)

So I can confirm what is happening when you try and consolidate the CSV files

If you do have the new combine binaries process it is defnitely more confusing for simple combinations.  It is bettter however for more complex combinations (which will actually help you!)

From reading the document you provided it seems that the 3 separate steps can actually all be done in Power Query automatically now

Unfortunately I don't have the latest update to Excel with the new combine binaries installed on my machine (client compatabilty issues).  However here's a screenshot from Power BI Desktop which does the same thing.  I think the layout on the left hand side will look similar to what you have.

Let me know if it is similar

Thanks

[#6]

Also take a look at this article by Ken Puls

It may help you understand how things have changed

http://www.excelguru.ca/blog/2016/12/21/new-combine-binaries-experience/

[#7]

Hi Wyn, Took a look at the article and didn't find a resolution to my specific challenge. I've attached a detailed workflow that should also give you the information you requested. Let me know if you need more and again thanks for your help.

[#8]

Hello Cory,

I imported a folder with some copies of your text file into PowerQuery and attached the file to this post. The main steps I did are:

I created a query for one file first and setted it up, e.g. defining the delimiters as Wyn Hopkins did and changing the data types.The query is named qryImportFile in the sample file. Then I created the parameter Path, where you can input the path to your folder. On my computer this was D:\Downloads\Cory\Files. After then, I created from the first query qryImportFile a function, where I parametrized the filename and path. The function name is fctImportFile. If you look and compare the M-Code of the function with the query qryImportFile, then you see, that they are very similar and just wrapped by another statement. The next step was to create a query for importing a folder. This query is named qryImportFolder. There, I added a new user defined column and applied the function on it. This may be a little bit old style, but works.

Please let us know, if the attached Excel file including the queries works for you after changing the path. The path should have a trailing backslash.

Best,

Mourad

[#9]

Hi Corey

I've pulled together a step by step guide of what you need to do in the attached excel file

(my screen shots are from Power BI desktop but it should be the same for Excel)

[#10]

Hi Wyn, Are you importing a single text file or multiple (this is what I am trying to do for the consolidation)? I get the options you reference when importing a single file but not when importing multiple text files from a folder. There is no gear even present.

[#11]

I believe I may have been able to get this to work using the queries you created along with the pre-work completed by Wyn Hopkins. I will try the consolidation tomorrow and confirm the numbers appear to line up and report back. Thanks all!

[#12]

Hi Corey,  yes I'm combining from a folder

Try replacing the line of code in the formlua bar with this (it may be that the Power BI desktop version i'm using is not consistent with your version of Power Query in Excel).  If that's the case then unfortunately I'm not going to be of much further help

= Csv.Document(#"Sample Binary Parameter1",7,{0,13,36,52,74,88,107},null,1252)

[#13]

Hi Corey

I've just downloaded the latest version of Power Query into Excel 2013 and the source cog is showing on my version?

What version of Excel and Power Query are you using?

[#14]

Hi Wyn, I do see a new version out here, as of 03/01, and have downloaded it. This version is much better, but the process is still not as streamlined as before. With your help and instructions, I have the latest version incorporating the modifications you provided and I am back up and running. I really appreciate all of the help! Regards, Cory