Forum Discussion

AnilKumar's avatar
AnilKumar
Helper II
6 years ago
Solved

repeat column values based on another column

Hi all,

I am Struggling since long back. I have data like 

Order numoriginal dateactual dateinvoiced amt times
Order1June-2016June-20161
Order1sept-2016march-20173
Order1dec-2016march-20182
Order1march-2017  
Order1june-2017  
Order1sept-2017  
Order1dec-2017  
Order1mar-2018  
Order1june-2018  

 

What i Need is a new column which will be like,

Order numoriginal dateactual dateinvoiced amt timesNEw Colum
Order1June-2016June-20161June-2016
Order1sept-2016march-20173march-2017
Order1dec-2016march-20182march-2017
Order1march-2017  march-2017
Order1june-2017  march-2018
Order1sept-2017  march-2018
Order1dec-2017   
Order1mar-2018   
Order1june-2018   

 

So based on the No.of times column, the new column shold be derived.

 

Please suggest an idea.

 

Thanks in Advance.

Regards,

Vishnu priya

Data Modeling
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    6 years ago

    Hi AnilKumar, 

     

    You can create a index column in Query Editor firstly: 

     

     

    Then create calculated columns below: 

     

    EndRowNum = CALCULATE(SUM('Table'[invoiced amt times]),FILTER('Table','Table'[Index]<=EARLIER('Table'[Index])))
     
    StartRowNum = 'Table'[EndRowNum]-'Table'[invoiced amt times]+1
     
    Column = CALCULATE(MIN('Table'[actual date]),FILTER('Table',EARLIER('Table'[Index])>='Table'[StartRowNum]&&EARLIER('Table'[Index])<='Table'[EndRowNum]))
     
     

    Best Regards,
    Qiuyun Yu 

4 Replies

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Community Support
    6 years ago

    Hi AnilKumar, 

     

    You can create a index column in Query Editor firstly: 

     

     

    Then create calculated columns below: 

     

    EndRowNum = CALCULATE(SUM('Table'[invoiced amt times]),FILTER('Table','Table'[Index]<=EARLIER('Table'[Index])))
     
    StartRowNum = 'Table'[EndRowNum]-'Table'[invoiced amt times]+1
     
    Column = CALCULATE(MIN('Table'[actual date]),FILTER('Table',EARLIER('Table'[Index])>='Table'[StartRowNum]&&EARLIER('Table'[Index])<='Table'[EndRowNum]))
     
     

    Best Regards,
    Qiuyun Yu 

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    6 years ago

    Hi,

    Based on the 3rd and 4th columns, you want to generate the 5th column.  What is the use of the 1st and 2nd columns?

    • AnilKumar's avatar
      AnilKumar
      Helper II
      6 years ago
      Hi,
      The first column is about the order number. There might be n number of orders. And the 2nd column tells the data about the month to be invoiced.
      Based on this only the no.of records of particular order depends.
      Thanks & regards ,
      Vishnu priya