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.
I have table :
Category | January | February | March | April | May | June | July | August | September | October | November | December |
A-B | 2887 | 570 | 1782 | 3633 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
C-D | 0 | 0 | 117 | 2264 | 1631 | 4304 | 3418 | 3605 | 4123 | 0 | 0 | 0 |
E,F,G | 0 | 0 | 0 | 0 | 0 | 0 | 3558 | 3008 | 2613 | 2617 | 4813 | 188 |
H-K | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 602 | 1204 | 1011 | 2699 |
L | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 380 |
While splitting column 'Category' by delimiter '-' I want that H-K should be split into H,I,J,K into different rows. How shall I do that?
Solved! Go to Solution.
Hi @Anonymous
Here is one way, you can get the list, then expand to new rows. Paste all in the Advanced Editor via blank query
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WctR1UorViVZy1nUB0646bjruYJaHrjeY9lGKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [a=Text.SplitAny([Category],"-,"),
b={List.Min(a)..List.Max(a)}][b])
in
#"Added Custom"
Hi @Anonymous
Here is one way, you can get the list, then expand to new rows. Paste all in the Advanced Editor via blank query
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WctR1UorViVZy1nUB0646bjruYJaHrjeY9lGKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [a=Text.SplitAny([Category],"-,"),
b={List.Min(a)..List.Max(a)}][b])
in
#"Added Custom"
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.