Files With Different Columns Changing to Null (Transform Function)

[Anonymous]

Hi Everyone,

I'm currently referencing this video: Combine Files With Slightly Different Columns (Power BI & Excel). I've successfuly implemented the solution of updating the transform function code to include all needed column headers. However, now my data is changing to NULL...

Any and all suggestions are helpful! 

Please see the below for original data vs. outcome image. 

If not... please explain	Was any equipment Red Tagged for Repair?
N/A	blank
N/A	Yes
Needs possible software reset	Yes

The sample data is transformed through the combination/transformation of multiple excel files. The actions to modify the below transform function are due to one column being excluded upon combining the files. The "Columns" list inserted into the function below adds the necessary headers and is working as intended albeit the NULL issue. 

Thank you,

PBInJelly

[Anonymous]

Hi @Anonymous ,

What's your sample data? Perhaps you can follow the description of this article to check whether your steps are correct step by step.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

You have to make sure that, before you combine all of the tables, that the table with the most columns is at the top. You can use Table.ColumnCount to add a column with the number of columns from each unexpanded table. Add an index column first so as not to lose your original sort order. Now, as long as your first table has column names and values, you will eliminate null columns.

--Nate

[Greg_Deckler]

@Anonymous Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[Anonymous]

Greg, I updated the post accordingly. Hopefully this is helpful for you... 

Thanks, 

PBInJelly