Issue while importing binary data. combine files feature makes other columns disappear

[Anonymous]

I have been trying to import a new dataset into my power bi report, and I have been having some issues with the data being in a binary format. I am reletively new at Power Query so I may have missed something, but these photos will best illustrate the problem. (seen below)

The file upon importing (untouched)

This is how the data looks upon importing, where a majority of the columns are in a binary format. The only way I have been able to transform the column into the correct data format is the "combine files" feature, where it lets me input the correct data transfer format (#37). I am new at Power Query so I may have missed something, but this was the only way I could get it to work

The correct settings to transfer from binary data

Then I click okay, and I am left with the same table, but only one column. I need to do this to the majority of columns in this dataset, is there any way you guys would advise to do this? here is the result below.

the resulting column (which is correct) but it removes all other columns.

Let me know what you guys think!! thanks for your time.

Marco Charbonneau

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[Anonymous]

@lbendlin Thanks for taking a look at my problem, but I am relatively new at power BI and I dont know how to build and share a table while ensuring it stays as binary data.

I am importing this directly from the database, and all the data is in binary format, so I dont know what is contained until I transfer the data format to the right file origin (#37 IBM EBCDIC. For example, the first row in my table should Contain many rows with "HP" seen from the screenshots earlier). The table is quite large (over a million rows) so I built a sample set of the first 25 rows in the table. When trying to export that data from Power BI, the Webpage gives a result that shows the entries incorrectly (1's and 0's) rather than the actual binary data. (As seen below). I still added the file (Link: New Report (1).xlsx ), but let me know if this is a visual bug, or there is another method I can use to share the actual data with you.

Sorry for all the text, I just want to provide as much detail as possible! Thanks again,

Marco C.

Data when viewed through the website after publishdata as seen in power query editor

[lbendlin]

It would be better if you could post a sample raw file somehow. Understand that it might be difficult to sanitize.

[Anonymous]

Oh, I think I might have a way to do that. let me know if this works as well, Thanks!

Link: Test-Data.xlsx

Note* Some of the rows are in the correct format already, the only ones I need to transform have the "Binary.From..." for each of the fields.

Thanks for the quick response!

[lbendlin]

sorry, I meant the actual raw file.  The Excel version only has the formulas without data.

[Anonymous]

Dont know quite how to do this, so I tried an external tool to export the data-- but it still puts it in a ".csv" file with (what I assume to be random) numbers for each spot. (See here: Data-OEP55.csv ) Do you have a way to export raw data? All I can find online are ways to export to excel files.

[lbendlin]

yeah, this is really tricky.  Clicking on the link opens the file in Excel online (for me) and curl gets sent around a couple of redirects until it hits a 403.   At this point I'm out of ideas.  Could you use a public file service for that?  Understand if not.

[Anonymous]

Working with my boss to see if we can get the raw file from another source instead of through power bi.