Forum Discussion
Create multiple rows from single row based on conditions
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
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
Thank you very much for your help. You make it so easy.
My apology for not stating I want the solution in PQ.