cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Borja204
Helper II
Helper II

Join and expand 2 tables

Hi! 

 

I'm kinda new to power query and I am struggling with something that maybe simple:

 

Giving this 2 tables:

 

UsersPermissions

User1005
User2010

 

PermisionsTablePath

005005
010005|010
020005|010|020

 

I'd need to obtain this table in power query:

 

User1005
User1010
User1020
User2010
User2020

 

How could I achieve this?

 

Thanks!

1 ACCEPTED SOLUTION
CNENFRNL
Super User III
Super User III

Based on the limited sample dataset,

let
    PermissionPath = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjAwVdIBk7E6QJ6hAYRXA2KBRYyQRGpAvNhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Permission = _t, Path = _t]),
    #"Sorted Rows" = Table.Sort(PermissionPath,{{"Path", Order.Ascending}}),
    #"Transformed Path" = Table.TransformColumns(Table.RemoveColumns(#"Sorted Rows",{"Permission"}), {"Path", each Text.Split(_, "|")}),
    UserPermission = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi1OLTJU0lEyMDBVitWB8I1AfEMDpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [User = _t, Permission = _t]),
    #"Selected Path" = Table.TransformColumns(UserPermission, {"Permission", each let l=List.Last(List.Select(#"Transformed Path"[Path], (l) => List.Contains(l,_))), pos=List.PositionOf(l, _) in try List.RemoveRange(l, pos-1) otherwise l}),
    #"Expanded Permission" = Table.ExpandListColumn(#"Selected Path", "Permission")
in
    #"Expanded Permission"

Screenshot 2021-05-16 233351.png

View solution in original post

2 REPLIES 2
Borja204
Helper II
Helper II

Thanks!!!

CNENFRNL
Super User III
Super User III

Based on the limited sample dataset,

let
    PermissionPath = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjAwVdIBk7E6QJ6hAYRXA2KBRYyQRGpAvNhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Permission = _t, Path = _t]),
    #"Sorted Rows" = Table.Sort(PermissionPath,{{"Path", Order.Ascending}}),
    #"Transformed Path" = Table.TransformColumns(Table.RemoveColumns(#"Sorted Rows",{"Permission"}), {"Path", each Text.Split(_, "|")}),
    UserPermission = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi1OLTJU0lEyMDBVitWB8I1AfEMDpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [User = _t, Permission = _t]),
    #"Selected Path" = Table.TransformColumns(UserPermission, {"Permission", each let l=List.Last(List.Select(#"Transformed Path"[Path], (l) => List.Contains(l,_))), pos=List.PositionOf(l, _) in try List.RemoveRange(l, pos-1) otherwise l}),
    #"Expanded Permission" = Table.ExpandListColumn(#"Selected Path", "Permission")
in
    #"Expanded Permission"

Screenshot 2021-05-16 233351.png

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors