How to extract multiple entries from a row

[sonaliverma]

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"

[Anonymous]

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 ?

[rohit_singh]

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! 🙂