Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Create duplicate rows based on criteria

Hi,   This is my first post and I am very new to Power BI. So any help is appreciated.   I want to duplicate rows of my data, based on certain criteria. My data looks like below (With few more co...
Need Help
  • tex628's avatar
    tex628
    4 years ago

    Hi Anonymous, 

    I would propose something along the following. 

    Start by making a reference to your calendar table in Power Query so you have a copy of the original table to work with. 

    Follow this up by using the DateTime.LocalNow (DateTime.LocalNow - PowerQuery M | Microsoft Docs) to identify the current period and Year. 

    Now we know that we want to remove everything apart from the following:

    "Period" - "Last year" - "Actual"
    "Period" - "Current year" - "Actual" 
    "Period" - "Current year" - "Forecast"

    So with the help of LocalNow you should be able to create a filter statement to remove the other rows. 

    The final step would be to create the "Desired Values" column, which you should be able to do by looking at the Year and Indicator columns. 

    Append the table back into the original table, and remove the load on the temporary one. 

    Let me know how it goes! 

    Br, 
    Johannes

tex628's avatar
tex628
Community Champion
4 years ago

Hi Anonymous, 

I would propose something along the following. 

Start by making a reference to your calendar table in Power Query so you have a copy of the original table to work with. 

Follow this up by using the DateTime.LocalNow (DateTime.LocalNow - PowerQuery M | Microsoft Docs) to identify the current period and Year. 

Now we know that we want to remove everything apart from the following:

"Period" - "Last year" - "Actual"
"Period" - "Current year" - "Actual" 
"Period" - "Current year" - "Forecast"

So with the help of LocalNow you should be able to create a filter statement to remove the other rows. 

The final step would be to create the "Desired Values" column, which you should be able to do by looking at the Year and Indicator columns. 

Append the table back into the original table, and remove the load on the temporary one. 

Let me know how it goes! 

Br, 
Johannes

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thanks tex628 

     

    I cretaed reference of my original table and applied filters on fileds. Then adjusted my DAX Column logic to adjust YTD values. Also created one more filed and tagged all values in this refrenced table as duplicate, so that I can use this as filter when I will have to show KPIs in viz. Then appended both tables and disabled load for previous both tables.

    Thank you for your support 🙂