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.
Hi
I have a report that's built off of a SP list. In this list there's a column [IT Resources]. This is being populated by using the Org's address book. And this filed also contains multiple values.
Currently the PBI report has a manually maintained a table of User Names and User IDs that's to be used for the report. I am trying to get away from maintaining this manual table, and start using the data that's coming directly from the SP.
Following the instructions from this link I have extracted the [IT Resources] names by expanding the [FieldVAluesAsText] -
- https://whitepages.unlimitedviz.com/2018/01/using-power-bi-to-report-on-person-fields-in-sharepoint/
Then I have also expanded the [IT ResourcesID] column by Extract Values.
I need both these fields.
I end up with
The ID's in column [IT ResourceId] are in sequence to the names in [FieldValuesAsText.IT_x0020_Resources].
How do I go about transposing these 2 columns so the right ID lines up with the right name? Once I get rid of the 1st column [ID] I want to end up with a unique list of users and their IDs.
I've tried spliting, say the [FieldValuesAsText.IT_x0020_Resources] column, and then Unpivoting these columns. But then how do I treat the [IT ResourceId] to line up with these new rows without duplicating.
Hoping someone has a (better) solution.
Thank you
Solved! Go to Solution.
Here's one way to do it in the query editor. To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXK0drJ2BtEFBTmp1k6JeUBo7ZxYVJRfohSrE61kBJKDyYNFjIE8F2tXEJmfbu2ak1qQkZgHVBwLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, ResourcesID = _t, ResourcesNames = _t]),
#"Added Custom" = Table.AddColumn(Source, "Custom", each List.Transform(List.Zip({Text.Split([ResourcesID], ";"), Text.Split([ResourcesNames], ";")}), each [ResID = _{0}, ResName = _{1}])),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"ResourcesID", "ResourcesNames"}),
#"Expanded Custom1" = Table.ExpandRecordColumn(#"Removed Columns", "Custom", {"ResID", "ResName"}, {"ResID", "ResName"})
in
#"Expanded Custom1"
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Here's one way to do it in the query editor. To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXK0drJ2BtEFBTmp1k6JeUBo7ZxYVJRfohSrE61kBJKDyYNFjIE8F2tXEJmfbu2ak1qQkZgHVBwLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, ResourcesID = _t, ResourcesNames = _t]),
#"Added Custom" = Table.AddColumn(Source, "Custom", each List.Transform(List.Zip({Text.Split([ResourcesID], ";"), Text.Split([ResourcesNames], ";")}), each [ResID = _{0}, ResName = _{1}])),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"ResourcesID", "ResourcesNames"}),
#"Expanded Custom1" = Table.ExpandRecordColumn(#"Removed Columns", "Custom", {"ResID", "ResName"}, {"ResID", "ResName"})
in
#"Expanded Custom1"
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hi @Anonymous
If I understand it correctly, you need something like this? id vs its value without duplicates? And the original two columns are List? then you can try this way, then you can Expand to New Rows, and split them by delimiters
Table.AddColumn(yourPreviousStep, "Custom", each List.Transform( List.Zip({[yourIDColumn],[yourValueColumn]}),each Text.Combine(_,"=")))
IT Resourcesid | FieldValuesAsText |
30 | a |
1573 | b |
1721 | c |
… | … |
86 | d |
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.