Forum Discussion
Create multiple rows from single row based on conditions
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,
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
3 Replies
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
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