Split row based on if statement

[Anonymous]

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:

[Anonymous]

Thanks Spyros! That worked well. The "Amount" column formatted into text and i had to replace Indexing step as last step obviously. 

[Smauro · Accepted solution]

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

[Anonymous]

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.