Forum Discussion

LarsP's avatar
LarsP
Regular Visitor
9 years ago
Solved

Combine binaries returns null for columns with different header name

I have 3 excel files that I want to combine into one table, they have slightly different column names. When I use Combine binaries it returns null for columns with different header name. Same result ...
  • v-jiascu-msft's avatar
    v-jiascu-msft
    9 years ago

    Hi LarsP

     

    I am afraid this has to be done by hand. This is cited from official document: powerbi-desktop-combine-binaries

    • Transformations were not considered for each individual file before the files were combined into a single table. As such, you often had to combine files, then filter out header values by filtering rows as part of the edit process.
    • The Combine binaries transform only worked for text or CSV files, and didn't work on other supported file formats such as Excel workbooks, JSON files, and others.

    BTW, combining Excel workbooks isn't support by now. If you'd like remove a row in the middle of the table, you could use "Remove alternate row".

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Best Regards!

    Dale

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
9 years ago

Hi LarsP,

 

Maybe we could say Power BI is smart. We can do it this way. 

1. We do "use headers as first row" in the three table. So they almost have the same headers.

2. Append the three tables.

3. Delete the two rows of headers and "Use First row as headers".


 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

LarsP's avatar
LarsP
Regular Visitor
9 years ago

Hi

Thanks for the suggestion. I have tried and fail on something embarrasing simple. How do I delete a marked row? I can only find remove top rows, bottom rows, etc.

One way would be to remove the headers before append. Can this also work with combine binaries? Im trying to automate as much as possible.

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    9 years ago

    Hi LarsP,

     

    I should have done it better. We can delete it first before appending. Keep only the headers we want. Delete the other two headers.

     

    Best Regards!

    Dale

    • LarsP's avatar
      LarsP
      Regular Visitor
      9 years ago

      Thanks Dale

      And how about Combine Binaries? How do you solve it there?

      /LarsP

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Microsoft Employee
        9 years ago

        Hi LarsP

         

        What do you mean "Combine Binaries"? Just use "Append queries" in the Query Editor?

         

        Best Regards!

        Dale