Power Query Custom Column

[Anonymous]

I'm trying to add a custom column in Power BI Power Query Editor but I'm having some difficulty.
The table below is an example of 2 columns I have and 1 column I require.

I need a Yes to be in the Y/N column whenever an ID does NOT have a DOI assigned. ID can have duplicates, so when any of the same ID's has a DOI then it will be NO, else Yes.

Thanks

[v-yanjiang-msft]

Hi @Anonymous ,

According to your description, here's my solution. Please copy-paste the code in a blank query to see the applied steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVNJRSlSK1YGxdbGwjYhgGxPBNkGyCxfbFEm9KZo4LjZMvRkOtjkRbAsktiWS+ZZo4kB2LAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, DOI = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"DOI", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","-","",Replacer.ReplaceText,{"DOI"}),
    #"Merged Queries" = Table.NestedJoin(#"Replaced Value", {"ID"}, Table.Group(#"Replaced Value", {"ID"}, {{"Max", each List.Max([DOI]), type nullable text}}), {"ID"}, "Max", JoinKind.LeftOuter),
    #"Expanded Max" = Table.ExpandTableColumn(#"Merged Queries", "Max", {"Max"}, {"Max"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Max", "Custom", each if[#"Max"]=""then"Yes"else"No"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Max"})
in
    #"Removed Columns"

Get the result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi, thanks for your reply. It does work for the example I provided, but when I try and use the code on the actual data I want to run the query against, It puills through the example data again.

How woudl I get this query to work with other, similar data with the same headings?

Thanks

[v-yanjiang-msft · Accepted solution]

Hi @Anonymous ,

Please follow the detailed steps:

1.Select the DOI column, right click and select Replace Value, replace - with "".

2.Right click the Table and select Duplicate.

3.In the new table, select ID column then click Group By.

Set it like below.

Get the max value for each ID. If all the DOI rows are blank of the same ID, its max value is also blank.

4.Select the original table then click Merge Queries.

Set it like this:

5.Now, in code of the Merged Queries step in the Table, replace the #"Table(2)" with the last step code from the table(2)

Get this, then you can delete the Table(2).

6.Expand the new column with the Max field.

7.Add a custom column.

if[#"Table (2).Max"]=""then"Yes"else"No"

Then remove the unneeded column, get the expected result.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Great work. Thank you very much!