PowerBI display value based on value in another column

[Gerald_VACE]

Hi all,

i am relatively new to PowerBI and i dont know if its possible to achieve - maybe you guys can help me out! (i hope so 🙂 )

I imported a CSV File (data comes from an API Script) and transformed it by examples (to get the right values to the correct headers).

So far so good.

This is how it looks like:

Every Group is in a Parent group - indicated by the "parentGroupUuidGroup" Column/Value.

What i want to achieve is that i get a new column with the assigned parent group name (i try to "link" them).

The data of the parent Groups are also in the above Columns.

Is there a possible way to do this on this data basis?

Any help would be much appreciated!

Many thanks!

Regards

Gerald

[Gerald_VACE]

I have about 450 rows. 
Can you give me a hint how to actually achieve this in PowerQuery?

These are my first steps with PowerQuery 🙂

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Gerald_VACE]

Here are some example Data:

displayName	uuid	parentGroupUuid
All	00000000-0000-0000-7001-000000000001	nil
Lost & found	00000000-0000-0000-7001-000000000002	00000000-0000-0000-7001-000000000001
Windows computers	00000000-0000-0000-7015-000000000001	8043ada4-fed2-428f-9b40-c4c5f0f587b1
ZZZ_Standard dynamische Gruppen	8043ada4-fed2-428f-9b40-c4c5f0f587b1	00000000-0000-0000-7001-000000000001
Linux computers	00000000-0000-0000-7015-000000000002	8043ada4-fed2-428f-9b40-c4c5f0f587b1

Here is how it should look like:

displayName	uuid	parentGroupUuid	parentGroupDisplayName
All	00000000-0000-0000-7001-000000000001	nil	- ("All" is the top group)
Lost & found	00000000-0000-0000-7001-000000000002	00000000-0000-0000-7001-000000000001	All
Windows computers	00000000-0000-0000-7015-000000000001	8043ada4-fed2-428f-9b40-c4c5f0f587b1	ZZZ_Standard dynamische Gruppen
ZZZ_Standard dynamische Gruppen	8043ada4-fed2-428f-9b40-c4c5f0f587b1	00000000-0000-0000-7001-000000000001	All
Linux computers	00000000-0000-0000-7015-000000000002	8043ada4-fed2-428f-9b40-c4c5f0f587b1	ZZZ_Standard dynamische Gruppen

I have a total of up to 4 levels of these groups.

It looks like this (everyone of these levels is a group):

All

- Customer 1

-- Group 1

--- possible Subgroup

-- Group 2

-- Group 3

--- possible Subgroup

- Customer 2

It should always output the "Customer" group in the "parentGroupDisplayName" Column.

Many thanks in advance!

Regards

Gerald

[lbendlin]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lY+xCsIwEIZfJWRu4BpTW0cnl24OQmuRNJdgoU1K06C+vYpCQRDSf7jp/vvuq2u673uaUPiGLSMHSBksSV9rtElqWjo/k3MA4FtiXLAY1+exmDfj1Fl0N0+UG8Yw68n/K6fZ748FiI1EKZjRyJnghWG7VgBTQmUGTFbk7YdRVdXlOEuLckKCDyuHzqurJocpjKO2sadWWJWdDfe1TjzaqXkC", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [displayName = _t, uuid = _t, parentGroupUuid = _t]),
    #"Merged Queries" = Table.NestedJoin(Source, {"parentGroupUuid"}, Source, {"uuid"}, "Changed Type", JoinKind.LeftOuter),
    #"Expanded Changed Type" = Table.ExpandTableColumn(#"Merged Queries", "Changed Type", {"displayName"}, {"parentGroupDisplayname"})
in
    #"Expanded Changed Type"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

Note: this is a purely academic exercise.  You don't really need to do this lookup here, or at all.  Use the PATH functions in DAX.

[Gerald_VACE]

Thanks for the provided informations and the code.

Didnt have much time lately to give this a test - hope i find some time in the next days 😞

I will let you know if it solved it.

Many thanks for your help!!

[lbendlin]

How many rows? If not too many then you can join the table with itself in PowerQuery to look that up.  If a lot then yu need to look at DAX options like LOOKUPVALUE or TREATAS.