Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I have a column called envID, with duplicate values, one envID can have multiple service owners. I want to remove the duplicate envID's but not lose the service owners, as it will randomly pull one through. I want to create a custom column when removing duplicates to append the services owners together in one column associated to the envID. Below is an example of the data, and the desired outcome.
Thank you 🙂
Solved! Go to Solution.
Hi @HarveyM ,
Right click on column "envID">select “group by”:
Then fill in as shown below:
Then add a custom column as below:
= Table.AddColumn(#"Grouped Rows", "Custom", each Table.Column(Table.Distinct([ALL]),"Service Owner"))
Finally choose extract values:
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my reply as a solution!
Is it possible that you are putting this entire line in the Custom Column formula box ?
Table.AddColumn(#"Grouped Rows", "Custom", each Table.Column(Table.Distinct([ALL]),"Service Owner"))
which you don't want to do.
You just want to put this in the formula box of the Custom Column:
Table.Column(Table.Distinct([ALL]),"Service Owner")
The confusion could be caused by Kelly's providing the line as it is in the Formula Bar (not as it is in the Custom Column dialog). Let us know.
Is it possible that you are putting this entire line in the Custom Column formula box ?
Table.AddColumn(#"Grouped Rows", "Custom", each Table.Column(Table.Distinct([ALL]),"Service Owner"))
which you don't want to do.
You just want to put this in the formula box of the Custom Column:
Table.Column(Table.Distinct([ALL]),"Service Owner")
The confusion could be caused by Kelly's providing the line as it is in the Formula Bar (not as it is in the Custom Column dialog). Let us know.
Another approach, using @Greg_Deckler code, you can change from "Grouped Rows" as below
... i.e., after this line #"Removed Duplicates" = Table.Distinct ...
change to
#"Grouped Rows" = Table.Group(#"Removed Duplicates", {"envID"}, {{"Name - CSV", each Text.Combine([Service Owner], ","), type text}})
in
#"Grouped Rows"
@HarveyM Try:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRX0lHySCwqS61UitUhku+UmgfmGBsBOV75xRm4eD6JRUWVGNxYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [envID = _t, #"Service Owner" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"envID", Int64.Type}, {"Service Owner", type text}}),
#"Removed Duplicates" = Table.Distinct(#"Changed Type"),
#"Grouped Rows" = Table.Group(#"Removed Duplicates", {"envID"}, {{"Data", each _, type table [envID=nullable number, Service Owner=nullable text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Column([Data],"Service Owner")),
#"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Data"})
in
#"Removed Columns"
This worked perfectly when I ran it for the example data. However I am having troubles when trying to replace the example data with my Power BI columns, would you be able to post an eddited version where you indicate what should go where please.
Thank you 🙂
Hi @HarveyM ,
Right click on column "envID">select “group by”:
Then fill in as shown below:
Then add a custom column as below:
= Table.AddColumn(#"Grouped Rows", "Custom", each Table.Column(Table.Distinct([ALL]),"Service Owner"))
Finally choose extract values:
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my reply as a solution!
Also worth mentioning @v-kelly-msft that when I download your pbix file, remove the step where you added the custom column and then re-add it with the exact same formula, I get a different result, instead of a list I get a table created.
I can't get this to work, as every time I create the custom column it creates it as a table, instead of a list meaning I can't extract values.
I have tried a few different methods of getting it to create a list instead of a table but have had no luck. Do you have any pointers on this ?
Thank you
Hi @HarveyM ,
What @HotChilli considered is correct,the expression I showed you is a new query in the formular bar,not in the Custom Column dialog.
In dialog,it should be as below:
=Table.Column(Table.Distinct([ALL]),"Service Owner")
Once you will get a distinct list from each ID using the above expression,then expand it,you will get the final result.
Best Regards,
Kelly
Did I answer your question? Mark my reply as a solution!