Create multiple rows from single row based on conditions

[Anonymous]

Hi all,

I would like to turn the following table:

Criteria	Amount	Month
Up	1,000	7
Down	500	3

into the following table:

Criteria	Amount	Month
Up	1,000	7
Up	1,000	8
Up	1,000	9
Up	1,000	10
Up	1,000	11
Up	1,000	12
Down	500	3
Down	500	2
Down	500	1

If the criteria is "Up", then I will like to create multiple rows with value from the current month value (7) to 12 (the max month of the year).

If the criteria is "Down, then I will like to create multiple rows with value from the current month value (3) to 1 (the min month of the year).

Thank you very much for your guidance in advance.

Best regards,

[AlB]

Hi @Anonymous 

You could do it also in PQ, but I believe it will be faster in DAX. Create a calculated table, where Table1 is the first table you show:

NewTable1 =
GENERATE (
    SUMMARIZE ( Table1, Table1[Criteria], Table1[Amount] ),
    VAR criteria_ =
        CALCULATE ( DISTINCT ( Table1[Criteria] ) )
    VAR month_ =
        CALCULATE ( DISTINCT ( Table1[Month] ) )
    RETURN
        GENERATESERIES (
            IF ( criteria_ = "Up", month_, 1 ),
            IF ( criteria_ = "Up", 12, month_ )
        )
)

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB · Accepted solution]

@Anonymous 

If you want it in PQ, paste this M code in a blank query to see the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi1Q0lEyNDAwAFLmSrE60Uou+eV5QI4pWMhYKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Criteria = _t, Amount = _t, Month = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Criteria", type text}, {"Amount", Int64.Type}, {"Month", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Numbers(if [Criteria]="Up" then [Month] else 1 , if [Criteria]="Up" then 12-[Month]+1 else [Month] )),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Month"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns", "Custom"),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Custom",{{"Custom", "Month"}})
in
    #"Renamed Columns"

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

Thank you very much for your help. You make it so easy.

My apology for not stating I want the solution in PQ.