Combine Columns for Query

[ptmuldoon]

I am still pretty new at learning my way around Power Query and M, and hoping some of you with more experience could help prvoide some tips and ideas.

I am working to pull various accounting data via our office's 3rd party provider API.  In working to do so I can pull the "Forecast", "Budget" and "Actuals", and those are with the datsSetID parameter of 1, 2 and -3, respectively.

And I figured out how to pull these under 3 separate queries. But I would much prefer to have The Forecast, Budget and Actuals as columns all in the same table, possibly all with 1 query.

As an example, the query below is getting October 2020 Actuals; dataSetId -3. I then did some filtering and renamed the "stat" and "amt" columns to "actual stat" and "actual amt"

Now I want to basically add a "budget stat" and "budget amt" and similar for the forecast numbers.  I did experiement with merging and appending the queries but not have figured it out yet. I don't want to really add another X thousand rows, but rather just get the budget and forecast data into additional columns.

The one thing I think could be an issue is that a value could exist for the Actual and not exist for Budget or Forecast. Similar the other way around and exist for Budget and $0 actual.

Again, I'm still new with Power Query and M. But have some experience with VBA and coding in general. Just trying to get my head around it all.

Thanks,
PT

[edhans]

Great. If you have any specific questions, I recommend a new thread with some good data for us to work with. Hope ot goes smoothly for you!

[ptmuldoon]

Thanks.  The simple thing I was not doing correctly earlier was actually grabbing all of the columns to have it compare when doing the merge.   I think I have it figured out and will be working some more on it hopefully this weekend.

[edhans]

You need to use the Merge feature. You can google how to do it, or you can share your two tables (or relevant sample data) and I can provide an example for you. I'll repost the info on getting data to us.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

[edhans · Accepted solution]

Hi @ptmuldoon - I am not clear on your requirements. There could be a terminology issue here.

• Combine/Append adds rows to the bottom. Think of adding more data to the bottom of a grid of data in Excel. That is how Append works. All column names must match exactly for things to be in the same column, including the case of the column. "Date" and "DATE" would be two different columns for example. Any columns that exist in Table A but not in Table B would be all null for those values in Table B and visa versa.
• Merge will add data to the right, so think of it as a VLOOKUP where you add extra columns. But you must have a key field to lookup/merge.

If you could show us some data so we can visualize what you are dealing with that would help.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

[ptmuldoon]

@edhans 

Thanks for getting back to me and let me try and work to sanitize a sample file to share.  

But yes, I am essentially attempting to add columns from one table to another.

So I have three queries that are identical and all return the exact same column names.  two of those column names are "stat" and "amt".   My goal is have the "stat" and "amt" for the Actuals, Budget, and Forecast queries all in the same table.

Like you mentioned, I could do it do it Excel with an Xlookup or Index/Match type formulas.  But I'm hoping to be able to combine/merge them in Power Query if possible