how to combine two tables under specific conditions

[ThomasWeppler]

Hi Power Bi community

I have a customer that wants to use last years budget for next year until they have uploade next years budget.

I have a budget table with
accountnumber [wholenumber]

Date [date]

amountindefaultcurrency[decimalnumber]

I have made a copy of this table I call next years budget. Added a filter so I only look at the current year and then added a year to the date.

This allows me to combie the two tables [budget] and [next years budget] and combined the two tables to get the wished effect. However I need to only combine the two tables if there isn't a budget for next year.
Any idea on how I can check this? All help is greatly appreciated.

[ThomasWeppler]

I found a solution to this problem.

1. I copied the budget in m. so I has two tables. then I added a colum equal = 1 to that table that I will use later.
2. I wrote this to in first budget table to check in their was uploadet any budgets for the next year.
= Table.AddColumn(#"Sorted Rows1", "Er næste år med", each if [Year] = Date.Year(DateTime.LocalNow()) +1 then 1 else null , Int64.Type)
3. I sorted the new colum and filled Down 
= Table.FillDown(#"Sorted Rows2",{"Er næste år med"})
This made it so every cell in the new row has 1 if the new years budget was posted and null if it wasn't.

4. I combined the two tables. 
5. I wrote this
= Table.AddColumn(Source, "Filter", each if [Er næste år med] = 1 then each if [Næste år] = 1 then 0 else 1 else 1, Int64.Type)
It checks if the new budget is uploadet and if not it does nothing, but if it is it uses the coulm = 1 from the first step to add a new colum where all the budgets from next year is equal to 0. than I filter all the zeros away and remove the new colums.

The result is that only add a copy from last years if they company hasn't uploaded a new budget yet, but as soon as they upload the new budget I use that insted.

Maybe I could have made it in a simpler way but I am pleased with the result.

[Anonymous · Accepted solution]

Hi @ThomasWeppler ,

In Power Query, there're append queries and merge queries which serve different purposes, and the choice between them depends on what you need to achieve with your data.

Append Queries
Use append queries when you want to combine rows from two or more tables into a single table. This is similar to stacking data on top of each other. It's useful when:

You have multiple datasets with the same structure (same columns) and you want to consolidate them into one.
For example, if you have sales data for different months in separate tables and you want to create a single table with all the sales data.
Merge Queries
Use merge queries when you need to combine columns from two tables based on a common column (key). This is similar to performing a join operation in SQL. It's useful when:

You need to bring in additional information from another table.
For example, if you have a table with sales transactions and another table with customer details, and you want to add customer information to each transaction based on a common customer ID.
Scenarios:
Append Queries:

Combining monthly sales data into a yearly sales table.
Merging survey results from different regions into a single dataset.
Merge Queries:

Adding customer names and addresses to a table of orders using a common customer ID.
Combining product details with sales data to include product descriptions and prices.

Combine queries - Tutorial: Shape and combine data in Power BI Desktop - Power BI | Microsoft Learn

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.

[Omid_Motamedise]

You can use Group By and make shoure for each customer curent data and budget for the next year are available

[lbendlin]

 until they have uploade next years budget.

Uploaded where? Please provide a more detailed explanation of what you are aiming to achieve. What have you tried and where are you stuck?

[ThomasWeppler]

The hard part was that I never knew when they would upload the next budget.