Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Anonymous
Not applicable

Duplicate values while merging columns

Hi all!

 

I have a Forecast table with Company/ Product/ Forecast columns:

 

alicia_mtz_0-1692892179893.png

 

I have another table with Product/ Sales but NO Company:

 

alicia_mtz_1-1692892320040.png

 

If I try to merge the Sales into the Forecast one, the values get duplicated because of the Company column.

 

 

alicia_mtz_3-1692892650745.png

 

Is there a way to merge it but putting all sales in the first occurrence of each Company and zeros in the other rows??

 

Thank you very much! 🙂

1 ACCEPTED SOLUTION

 

 

Hi alicia_mtz,

 

I've prepared the same result you are requesting by adding the company field in the sales table which stated "Nike" for all the rows, and by appending the sales table with the forecast table. I just used the PQ ribbons which produced the following M codes as I am not that conversant with M.  

Sakiko_1-1693210490517.png

 

View solution in original post

8 REPLIES 8
Anonymous
Not applicable

Hi @lbendlin ,

 

Thanks for your response! 🙂

 

Sorry, I explained it wrong and I just noticed after your question.

 

The data I had doesn't have info of Companies to split it in that way, so I just thought on putting all sales in the first row of each PRODUCT, like this:

 

alicia_mtz_0-1693207742975.png

 

Is this possible?

 

Thank you!

 

 

Hi alicia_mtz,

 

I've prepared the same result you are requesting by adding the company field in the sales table which stated "Nike" for all the rows, and by appending the sales table with the forecast table. I just used the PQ ribbons which produced the following M codes as I am not that conversant with M.  

Sakiko_1-1693210490517.png

 

Anonymous
Not applicable

Hi @DataNinja777 ,

 

Yes, that's what I want! However I'm trying to replicate your steps but I'm not getting the same results as you. 

 

This is what I get:

 

alicia_mtz_0-1693212524684.png

 

 

Can you post a screen of the Groupby step to see if I'm doing it correctly, please? 🙂  I'm not sure which step I'm doing wrong.

 

Thank you!

Hi alicia_mtz,

= Table.Group(#"Appended Query", {"Company", "Product"}, {{"Sales", each List.Sum([Sales]), type nullable number}, {"Forecast", each List.Sum([Forecast]), type nullable number}})

I copied and pasted that group by step above.  It was generated by clicking on the ribbons.

 

 

Anonymous
Not applicable

Hi @DataNinja777 

 

I've copied your line of code but it still gives me the same result, not your result, I don't know why 😞

 

alicia_mtz_0-1693213616432.png

 

Thanks

Hi alicia_mtz,

 

Looking at your file, I think your source table for the sales still do not contain the company field which two "Nike" rows, therefore, it is showing null the top two lines.  What I did was to assign "Nike" in the source data for the sales table, in the additional row created with the name "Company", so that forecast table and sales table can be just apended. If you have any problem, please feel free to attach here your pbix file.  I will be able to fix it for you.  

Anonymous
Not applicable

Got it now! Thank you! 🙂

lbendlin
Super User
Super User

 

 the first occurrence of each Company

 

You will need to define what you mean by that.  Row numbers are not directly exposed in Power Query, so you need to either add indexes or implement a manual row check.

 

 

Is there a way to merge it but putting all sales in the first occurrence of each Company

 

All sales of what? each product? That's what you already did.  How are you planning to split the sales across the companies?

 

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.