Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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,
Solved! Go to 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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.