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.
Solved! Go to Solution.
You could start like this
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("vZHdbsIwDIVfpeLyaBd20qTJZWkDRKOho2UaQrz/a8wdjLYw7edmUeQL+8T2+XI6LboeTucgIpZgiOlNKSp5cX46LT7Ccp2kAoYilksWEgwT5OSjbB9W0A5cEBdmTNd9C00atft8X4zFl/6Ifgc1ZhKjNahjl8Ixi6nCKsIbbTU562aymLCsll120V51hWWlJ7vH1OEIdiBNMh/lfF+Sawz5wf2keSPdt2K42R26gCZWzzJAzsDpUd6GPWILbFoUnNvCk/d+0k2DjTXXPbdlqrN6H18DukPsQ8azyTnKVG5CbFCV8MrRBE3drAeKLGlmsdSMpU09GLyDnruvoQ/GeTt8lCPTzKwMuOSz+Ttc9kdcKcVww/Ug/3dcWggoi9VfcPF90d+KjvWvWAoK5XHxfn4H", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column1] <> "")),
#"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 0, 1, Int64.Type),
#"Split Column by Delimiter" = Table.SplitColumn(#"Added Index", "Column1", Splitter.SplitTextByDelimiter("*", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7", "Column1.8", "Column1.9", "Column1.10"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}, {"Column1.3", type text}, {"Column1.4", type text}, {"Column1.5", type text}, {"Column1.6", type text}, {"Column1.7", type text}, {"Column1.8", type text}, {"Column1.9", Int64.Type}, {"Column1.10", Int64.Type}})
in
#"Changed Type1"
and then add a mapping table for the values in the first two columns. Then it would be a question of separating the sbu entries in case you have a structure like in the example.
You could start like this
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("vZHdbsIwDIVfpeLyaBd20qTJZWkDRKOho2UaQrz/a8wdjLYw7edmUeQL+8T2+XI6LboeTucgIpZgiOlNKSp5cX46LT7Ccp2kAoYilksWEgwT5OSjbB9W0A5cEBdmTNd9C00atft8X4zFl/6Ifgc1ZhKjNahjl8Ixi6nCKsIbbTU562aymLCsll120V51hWWlJ7vH1OEIdiBNMh/lfF+Sawz5wf2keSPdt2K42R26gCZWzzJAzsDpUd6GPWILbFoUnNvCk/d+0k2DjTXXPbdlqrN6H18DukPsQ8azyTnKVG5CbFCV8MrRBE3drAeKLGlmsdSMpU09GLyDnruvoQ/GeTt8lCPTzKwMuOSz+Ttc9kdcKcVww/Ug/3dcWggoi9VfcPF90d+KjvWvWAoK5XHxfn4H", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column1] <> "")),
#"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 0, 1, Int64.Type),
#"Split Column by Delimiter" = Table.SplitColumn(#"Added Index", "Column1", Splitter.SplitTextByDelimiter("*", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7", "Column1.8", "Column1.9", "Column1.10"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}, {"Column1.3", type text}, {"Column1.4", type text}, {"Column1.5", type text}, {"Column1.6", type text}, {"Column1.7", type text}, {"Column1.8", type text}, {"Column1.9", Int64.Type}, {"Column1.10", Int64.Type}})
in
#"Changed Type1"
and then add a mapping table for the values in the first two columns. Then it would be a question of separating the sbu entries in case you have a structure like in the example.
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.