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 a table in Power BI in the following format
email, id, name, perm.0.email, perm.0.role, perm.0.type, perm.1.email, perm.1.role, perm.1.type,perm.2.email, perm.2.role, perm.2.type
I want to convert this table into the format
email, id, name, perm.email, perm.role, perm.type
How can I do that? The no of perm columns extends till 50 like perm.0.email.........perm.50.email etc.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSs1NzMzRUchM0VHIS8xN1VEoSC3K1TPQg4pDeUX5OQipksoCGMcQRZ0hsjpDZHVGKOqMkNUZgdUpxeoMIrfEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Split Column by Delimiter" = Table.SplitColumn(Source, "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", "Column1.11", "Column1.12"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Split Column by Delimiter", [PromoteAllScalars=true]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"email", " id", " name"}, "Attribute", "Value"),
#"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns",each [Attribute],each "perm " & Text.Range([Attribute],8),Replacer.ReplaceText,{"Attribute"}),
#"Added Index" = Table.AddIndexColumn(#"Replaced Value", "Index", 0, 1, Int64.Type),
#"Replaced Value1" = Table.ReplaceValue(#"Added Index",each [Index],each Number.RoundDown([Index]/3),Replacer.ReplaceValue,{"Index"}),
#"Pivoted Column" = Table.Pivot(#"Replaced Value1", List.Distinct(#"Replaced Value1"[Attribute]), "Attribute", "Value")
in
#"Pivoted Column"
How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".
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.
User | Count |
---|---|
100 | |
51 | |
19 | |
12 | |
11 |