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]

sorry for the late response.

Your code works, but when i change the source i get an error saying it expects an identifier before "let _t = ...".

My source is a csv File.

I also looked at the PATH function but it looks like, that this is not an option.

This was the command i used:

ParentGroupName = PATH(MAV_Groups[uuid],MAV_Groups[parentGroupUuid])

But it needed some extra, single values in my csv to get it working. That is not an option because the csv is generated automatically.

[lbendlin]

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[Gerald_VACE]

i cant provide more sample informations like i already did.
Also the outcome of your code is the outcome i want to achive.

This is the code from the original query:

let
    Quelle = Csv.Document(File.Contents("I:\UNC_PATH\CSV_FILE.csv"),[Delimiter=";", Columns=3, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
    #"Umbenannte Spalten" = Table.RenameColumns(#"Geänderter Typ",{{"Column1", "displayName"}, {"Column2", "uuid"}, {"Column3", "parentGroupUuid"}})
in
    #"Umbenannte Spalten"

"Quelle" = Source

To what do i have to change your code to make it work with the CSV as a source?

Your help is much appreciated!

[lbendlin]

But it needed some extra, single values in my csv to get it working. That is not an option because the csv is generated automatically.

All you need is to make sure that all parents are also listed as regular IDs.  I can show you the required Power Query code for that.

Try this:

let
    Quelle = Csv.Document(File.Contents("I:\UNC_PATH\CSV_FILE.csv"),[Delimiter=";", Columns=3, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
    #"Umbenannte Spalten" = Table.RenameColumns(#"Geänderter Typ",{{"Column1", "displayName"}, {"Column2", "uuid"}, {"Column3", "parentGroupUuid"}}),
#"Merged Queries" = Table.NestedJoin(#"Umbenannte Spalten", {"parentGroupUuid"}, #"Umbenannte Spalten", {"uuid"}, "Changed Type", JoinKind.LeftOuter),
    #"Expanded Changed Type" = Table.ExpandTableColumn(#"Merged Queries", "Changed Type", {"displayName"}, {"parentGroupDisplayname"})
in
    #"Expanded Changed Type"

Change the column references as needed.

[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.