Forum Discussion
Solved
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
| User1 | 005 |
| User2 | 010 |
PermisionsTablePath
| 005 | 005 |
| 010 | 005|010 |
| 020 | 005|010|020 |
I'd need to obtain this table in power query:
| User1 | 005 |
| User1 | 010 |
| User1 | 020 |
| User2 | 010 |
| User2 | 020 |
How could I achieve this?
Thanks!
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"
2 Replies
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"Thanks!!!
