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.
Hello,
Could anyone please help me in getting the all admin email ids from rows.
ex- "emailAddress":"groupUserAccessRight":"displayName":"identifier":"principalType" "VC_POWERBI@.onmicrosoft.com":"Admin":"VC_POWERBI":"VC_POWERBI@.onmicrosoft.com":"User" "grdier_azr@.onmicrosoft.com":"Admin":"Guillau Bordier":"gbordier_azr@.onmicrosoft.com":"User"
Hi @rohit_singh
Can you help me in case if i have hundreds of rows and and i have to extract the data for multiple employees like in Sonali's case she is only extracting for these 3 4 admin only . Suppose if I have id's 8 to 10 ?
Hi @sonaliverma
Please copy and paste the below M-code into a blank query to see the steps in detail.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hY5NCsIwEEavUnKAHsCVrYi4USn+LJpSYpLGgSQTJu2int4YF4II3c038z3etC3TToCtlCId44ozQziFS9RUSZk2DZjHyFk6KIjBivkgnM4ZlPYjDKApx0DgJQRhz3NIhYKz66Y/HW/bpt6vS/QOJGHEYSwlukxUyoHP07f5E/+D7++ywZBK+l48acmwm8BaMRU1ZiTvzB0X+I+Idd0L", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [String = _t]),
#"Added Custom" = Table.AddColumn(Source, "Custom", each {Text.Replace([String], """"&" "&"""", """"&"/"&"""")}),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"String"}),
#"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns", "Custom"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Expanded Custom", "Custom", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", type text}, {"Custom.2", type text}, {"Custom.3", type text}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value"),
#"Removed Columns1" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Removed Columns1", "Value", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Value.1", "Value.2", "Value.3", "Value.4", "Value.5"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Value.1", type text}, {"Value.2", type text}, {"Value.3", type text}, {"Value.4", type text}, {"Value.5", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type1", [PromoteAllScalars=true]),
#"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers",{{"emailAddress", type text}, {"groupUserAccessRight", type text}, {"displayName", type text}, {"identifier", type text}, {"principalType", type text}})
in
#"Changed Type2"
Input
Output
Column emailAddress will give you the data you need
Kind regards,
Rohit
Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂
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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |