Trouble merging queries & tables

[darynv_za]

I have two queries, one containing data and one containing the header data (column names but no data) for the data ( an SAP extract) I can format the one to get the data to be the column headers, I then want to merge(union) the second query, but everytime I try this I get either the extra columns added( when using append) or when I use merge I again don't get the required results.I have tried the all from both, I have tried the left only, right only, nothing seems to give me the required output of matching the headers from one file, with the data from the other.

[BhaveshPatel]

Hi There,

Why don't you try append option.

Appending Query 1 on Query 2.

This can solve your problem easily.

Thanks & Regards,

Bhavesh

[darynv_za]

I wish it was so simple, when I select append it adds the 31 coumns to the end as extra columns( so I have 31 column headers, then column1...column31), then populates my existing table with the first 31 columns of null all the way accross until it reaches "column1" and also creates records all the way down. I am still new to the Power BI space, so maybe there is something simple I am missing

[BhaveshPatel]

Hi There,

There are simple ways of filteration of null values and some other options as well. Can you please upload your sample file so I can guide you step by step process of achieving the final results.

Thanks & Regards,

Bhavesh

[darynv_za · Accepted solution]

HI Bhavesh

I tried a different route, which involved using the header file, leaving the headers as row 1, then appended the data (so row 1 was the header names I required and the column was called column 1, the same as it is in the data file). The appended data was then there, and THEN I promoted the first row to headers, so my row1 header data was now in the right place and all was working. 

Many thanks for the efforts to assist, I guess there are many ways to achieve the results required, sometimes it just needs a fresh approach.

[darynv_za]

Thanks for the offer, here is a small subset. the S_File is the headers, which I can remove the columns I don't need, then transpose so I am left with the headers I require(column called FIELDNAME) the other file is the data with no headers. So basically once I have the Query for the headers cleaned there is only DOC_Number....CML_CD_QTY, no values, and I then need to join the data (in the same order) to the query, but all the options I try do not give me the required results.