Forum Discussion
Split row based on if statement
Hi,
Im new to Power BI. I have the following table from a .xslx file
I want to split this in to 12 periods, only when period is "0". if period is 1 to 12 the row should just follow like this:
Hi Anonymous,
using the advanced editor, you could try adding this in your code:
#"Fix Amounts" = Table.ReplaceValue(PreviousStep,each if [Period] = 0 then [Amount] else null, each [Amount]/12,Replacer.ReplaceValue, {"Amount"} ),#"Fix Periods" = Table.TransformColumns(#"Fix Amounts",{{"Period", each if _ = 0 then {1..12} else {_}, type list}}),#"Expanded Periods" = Table.ExpandListColumn(#"Fix Periods", "Period")in#"Expanded Periods"Replacing PreviousStep with your previous step's name.
Best,
Spyros
3 Replies
Hi Anonymous,
using the advanced editor, you could try adding this in your code:
#"Fix Amounts" = Table.ReplaceValue(PreviousStep,each if [Period] = 0 then [Amount] else null, each [Amount]/12,Replacer.ReplaceValue, {"Amount"} ),#"Fix Periods" = Table.TransformColumns(#"Fix Amounts",{{"Period", each if _ = 0 then {1..12} else {_}, type list}}),#"Expanded Periods" = Table.ExpandListColumn(#"Fix Periods", "Period")in#"Expanded Periods"Replacing PreviousStep with your previous step's name.
Best,
Spyros
Hi smaru
I want to modify this a little bit. What if i use "x" as a delimiter like this:
In "period" column i type 5x36 meaning start at period 5, go for 36 periods. "Period" will have to go from 1-12 and "Year" has to be changed every 12 period.