Create duplicate rows based on criteria

[Anonymous]

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 columns). [DAX column] is a calculated column I have created with (if else condition). 

I want to duplicate rows where my period is current period (here for example current period is 5), for all the years in data. So for both 2021 and 2022. But with this, I also want to populate data in my [DAX column] for newly cretaed rows. But that data will not be same as main data.

So I want in total 3 rows for every month data:

Row 1- Where Period-5, Year -2021, [DAX column]: YTD LY

Row 2- Where Period-5, Year -2022 and Indicator- Actual,  [DAX column]: YTD Actual

Row -3 Wherev Period-5, Year -2022 and Indicator- Forecast,  [DAX column]: YTD Forecast

Year	Period	Indicator	DAX column
2021	1	Actual	YTD LY
2021	2	Actual	YTD LY
2021	3	Actual	YTD LY
2021	4	Actual	YTD LY
2021	5	Actual	MTD LY
2022	1	Forecast	YTD Forecast
2022	2	Forecast	YTD Forecast
2022	3	Forecast	YTD Forecast
2022	4	Forecast	YTD Forecast
2022	5	Forecast	MTD Forecast
2022	1	Actual	MTD Actual
2022	2	Actual	MTD Actual
2022	3	Actual	MTD Actual
2022	4	Actual	MTD Actual
2022	5	Actual	MTD Actual

Logic behind [DAX column]:

if
[Fiscal Period]=5 and [Fiscal Year]=2021 then "MTD LY"
else if
[Fiscal Period]=5 and [Fiscal Year]=2022 and [Indicator"]="Actual" then "MTD Actual"
else if
[Fiscal Period]=5 and [Fiscal Year]=2022 and [Indicator"]="Forecast" then "MTD Forecast"

else if
[Fiscal Period]<=5 and [Fiscal Year]=2021 then "YTD LY"

else if
[Fiscal Period]<=5 and [Fiscal Year]=2022 and [Indicator"]="Actual" then "YTD Actual"
else if
[Fiscal Period]<=5 and [Fiscal Year]=2022 and [ndicator"]="Forecast" then "YTD Forecast"
else null

Above logic is not wotking for YTD values, as i only have 1 row for current period and it is getting tagged for MTD. But current period will also be part of YTD calculation.

Desired Output

Year	Period	Indicator	DAX column	Desired column
2021	1	Actual	YTD LY	YTD LY
2021	2	Actual	YTD LY	YTD LY
2021	3	Actual	YTD LY	YTD LY
2021	4	Actual	YTD LY	YTD LY
2021	5	Actual	MTD LY	MTD LY
2021	5	Actual		YTD LY
2022	1	Forecast	YTD Forecast	YTD Forecast
2022	2	Forecast	YTD Forecast	YTD Forecast
2022	3	Forecast	YTD Forecast	YTD Forecast
2022	4	Forecast	YTD Forecast	YTD Forecast
2022	5	Forecast	MTD Forecast	MTD Forecast
2022	5	Forecast		YTD Forecast
2022	1	Actual	MTD Actual	MTD Actual
2022	2	Actual	MTD Actual	MTD Actual
2022	3	Actual	MTD Actual	MTD Actual
2022	4	Actual	MTD Actual	MTD Actual
2022	5	Actual	MTD Actual	MTD Actual
2022	5	Actual		YTD Actual

Thanks,

Shradha

[tex628 · Accepted solution]

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]

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 🙂