Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Duplicate values while merging columns

Hi all!

 

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

 

 

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

 

 

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

 

 

 

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! 🙂

Need Help
  • DataNinja777's avatar
    DataNinja777
    2 years ago

     

     

    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.  

     

8 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

     

     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?

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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:

     

     

    Is this possible?

     

    Thank you!

    • DataNinja777's avatar
      DataNinja777
      Super User
      2 years ago

       

       

      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.  

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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:

         

         

         

        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!