Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
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 🙂
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...
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
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.
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:
Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.
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!
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.
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!!
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.