Null values in merge queries after aggregating

[Andreaac]

Hi,

I have a project where I have 3 files: Sales File, Bikes Demand and Skates Demand. Sales Files combines information from both products' sales. 

I want to merge them into two queries to analyze the difference between demand and sales so I first merged my sales File with my Bikes Demand and everything worked perfectly... but when I did the same for my Sales and Skates Demand, and after I clicked on the aggregate>> Sum of sales option, some values appear as "null" and I don't know why this happens if right before that step, the preview of when I'm merging the queries displays the correct data.

Does anyone know why this could be happening?

[GinnMar]

Ooo was hoping for an answer to this . . . .I have the same issue, using the same method.  

[v-yetao1-msft]

Hi @Andreaac 

As you said you first merged your Sales file with your Bikes Demand and everything worked perfectly , did you merge to the original table or to a new table ? When you merged Sales and Skates Demand tables , are they the original tables or a new table that has been merged ? Which Join Kind you use when merge Sales and Skates Demand tables ?

I tried to create a sample to restore your scene and use Full Outer(all rows from both) join kind , you can see the final result that shown below . 1699 is the sum of the value that not in Demand table . Due to confindentiality , I have no way to judge your specific reason, maybe you can get some ideas from the sample I provided.

Best Regard

Community Support Team _ Ailsa Tao

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

[Andreaac]

Hi, thank you very much for your response. Here are my answers:

From my original sales and demand file y merged them into a new table (first for Bikes and works OK), when I tried to do the same for Skates, there are many rows where I do have the ProductID in both files (sales and demand) but still, when I aggregate them I still get many rows without the correct information and instead a "null" value... and at the top of this new table a blank row that sums all the values that it didn't match correctly.

I used a full outer join because there might be some products with demand but that weren't sold or viceversa (products that weren't contemplated in the demand but did sell). 

I have tried changing the data type, making sure that there are no errors between both tables and their related columns, but still can't create the correct merge.

Important information I should point out is that many ProductIDs have decimal numbers like: 1234.1 or 77635.1 so I'm not sure if that's something relevant to the problem.

My top questions are:

1. Do you know if this is a common issue?

2. Could there be any other reasons why Power BI is not matching the information correctly besides differences in data type?

3. Or perhaps, any suggestion to change my procedure and the able to obtain the desired table that I want using DAX?

[TheoC]

@Andreaac you are a trooper, that's for sure.  I'm going to tag a couple of the Power BI wizards into this message in hope that they are able to assist you. 

@mahoneypat @amitchandak @Ashish_Mathur @Greg_Deckler 

[Andreaac]

Actually, I have an update maybe this provides more information to analyze the problem:

Problem: Data not aggregated correctly after perfoming "Merge queries as new"

After I close and load Power Query and start working with the new merged query in my Model View and add the data in a table visualization I get the following:

Example:

ProductID	Description	Month	Category	Demand Qty	Sales Qty
					300
C765	Purple Bike	January	Bike	500	1928
B456	Red Skates	January	Skate	198	null
			Total	698	2228

I get a blank row that sums all the aggregated values that I have missing in my rows, and in the rows where the values should be aggregated I get "nulls"...

Many ProductIDs work fine and get the correct aggreated values but I can't find the difference between those that work and those that don't...

I hope this provides more information, and I really appreciate your help.

[Andreaac]

Thank you very much for your support!

[TheoC]

@Andreaac do all of your products have sales against them?  If not, the null values may represent no sales for the specific products.

Also, if the above doesn't assist, could you provide a screenshot or some dummy data?

Thanks heaps
Theo

[Andreaac]

Thank you for your response, actually yes there are some rows that have 0 value as sales, but that's no the problem since it worked perfectly for the first merge I created for Bike Demand + Sales.

The real problem is that for the second merge query (Skate Demand + Sales) for some rows that do have sales quantities, after creating the merge and selecting the aggregation step to sum the sales, the 'aggregated' new column displays a null value for some rows that do have sales values.

I cannot provide screenshots (due to confindentiality I'm using an example) but I'll give you a detailed explanation:

When I'm merging the queries I have the following information

Skates Demand

ProductID	Description	Month	Category	Demand Qty
A123	Blue Skates	January	Skate	500
B456	Red Skates	January	Skate	198

Sales 

ProductID	Description	Month	Category	Sales Qty
C765	Purple Bike	January	Bike	1928
B456	Red Skates	January	Skate	300

I match the columns: ProductID, description, month and category from both tables (using ctrl). With the option Left Outer joint, then click OK.

Then I select the button to expand/aggregate: "Aggregate" >> Sum of Sales 

And when the new column is created... the value in sales that I get for product B456 is null instead of 300 as it should be.

I hope this helps to get a clearer understanding of the problem. 

Thank you so much!

[Ashish_Mathur]

Hi,

Shouldn't you append instead of merge here?

[TheoC]

Hi @Andreaac 

That makes a lot more sense, thank you.

Are all the column formats matching prior to you running the merge?

[Andreaac]

Yes, they have the same "whole number" format 

[TheoC]

In terms of the Product IDs, are these system-generated or manually input?

[Andreaac]

The data comes from an excel file

[TheoC]

Before you undertake the Merge process, can you go to the Product ID of the Skate Demand and use "Format" on the Transform tab and "trim" the Product ID column?  It seems as though the issue might be a mismatch between the Product ID of the Skate Demand table given it worked in your first effort on the Sales for the Bikes Demand.

[Andreaac]

Update: I tried what you mentioned but didn't work 😓. Still getting null values, and also tried changing the data type for all the files but the same thing happens... 

Could there be any other thing causing the problem?

[TheoC]

@Andreaac that's not good.  It will be solved soon, there are some smart cookies in the Community.  One question, are you Merging Queries as New?

[Andreaac]

Thank you very much for all the support, and yes I created the queries as new.

[TheoC]

@Andreaac Sorry I couldn't be of more assistance! One last thing though, try to Merge with Full Join.

[Andreaac]

Don't worry, thank you for helping. I do hope to find the solution to this problem since it is weird that it worked for the previous query but not this one. 

Let's hope anyone knows how to solve this 🙂

[TheoC]

I am so sorry @Andreaac... 😞