Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 follo...
Need Help
  • Smauro's avatar
    Smauro
    5 years ago

    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

Smauro's avatar
Smauro
Solution Sage
5 years ago

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's avatar
    Anonymous
    Not applicable
    5 years ago

    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.