Custom column based on several other columns

[tgjones43]

This is a tricky one to describe, so here goes. I have attached a much simplified example. The data relates to fish sampling data in rivers.

I have a table with thousands of 8-digit numbers in the first column ([ID] - only one number is given in the example). Each 8-digit number occupies 30-40 rows, with data in several other columns. In one of those other columns ([ID 2]), and for some rows only, is another 8-digit number that is linked to values in other columns and is repeated several times. The value in [ID 2] is assigned when a new survey is undertaken, but the names in columns 3 and 4 can be the same, if the survey type is the same as the previous survey type (e.g. rows 4 and 5).

What I need to do is have some way of creating a custom column that assigns a value of Survey 1, Survey 2, Survey 3 to match the values in [ID 2] i.e. all values of 21779013 = Survey 1.

That would be easy if there weren't thousands of values in [ID], with each value of [ID] having unique values in [ID 2] i.e. for the next value of [ID] (21770162), the values in [ID 2] would be totally different, and unique to that particular value in [ID].

Does anyone have any ideas? Thank you!

[Greg_Deckler]

Given the data shown, what is the expected output? Also, can you post that data as text instead of a picture? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[tgjones43]

Thank you for the response. I have pasted the data below, with the column at the end showing the expected outcome.

ID	ID 2	Name	Value	Custom Column
21770161	null	Altitude	284	null
21770161	null	Width	5	null
21770161	21779013	Program	Core Fisheries Monitoring Program	Survey 1
21770161	21779155	Program	Core Fisheries Monitoring Program	Survey 2
21770161	21779296	Program	Water Framework Directive Programme	Survey 3
21770161	21779013	Purpose	Temporal	Survey 1
21770161	21779155	Purpose	Temporal	Survey 2
21770161	21779296	Purpose	Salmonid	Survey 3
21770161	21779013	Repeat frequency	1 year	Survey 1
21770161	21779155	Repeat frequency	1 year	Survey 2
21770161	21779296	Repeat frequency	6 years	Survey 3
21770161	21779013	Survey Strategy	Quanitative	Survey 1
21770161	21779155	Survey Strategy	Semi-quantitative	Survey 2
21770161	21779296	Survey Strategy	Semi-quantitative	Survey 3
21770161	21779013	Guideline Method	PDC Electric Fishing	Survey 1
21770161	21779155	Guideline Method	PDC Electric Fishing	Survey 2
21770161	21779296	Guideline Method	Electric Fishing	Survey 3

[Ola_S]

...a minor tweek to Lin's excellent solution, to match the 'expected outcome' above.

Kind regards, Ola

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZNRT8IwFIX/SrNnTFh1Qx4NiE8k6kx4IDw07AqN3TruWsz+vXdMjNal2WBPvevuOflu27NeBzycTMZhHAajILdK0fKgjDQ2BSr5/V2wGbU1rWRq9rRG7v+6nI7DWyqfUe9QZFTNNAJbyHIPKKFkS51Lo1HmO3buaXMJo2gAFz6N/7ishAFkC/qAT40fbC4RtkYe4eySgW8mi4Uu67N5g6zQKJQPvVvzN+FPcyJURrOlHoxXKEAY9o5wsJBvK9oKWQUCPTT9NA1UiyY+aUoPXGLxCBVLDNJR72rNixV0V6I+ZQ/gf10Cmbw5kNj41A3qpeqG+cnKFJTMgS3B7HVaX8h8xh4VvQ2U29Ozo5fmob/GoZmgxcGn5l0i+6upNbJ8kMjyYIjI8mCYyLozeVPoondr7hJZB6NT/Byafpo+kXXgOkfWAewZOgf1UvXFkXXor3HoEdnNFw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, #"ID 2" = _t, Name = _t, Value = _t]),
    #"Grouped and Add.SortNo" = Table.Group(Source, {"ID"}, {{"Count", each Table.AddIndexColumn(_, "Sort",1,1), type table}}),
    #"Expanded Count1" = Table.ExpandTableColumn(#"Grouped and Add.SortNo", "Count", {"ID 2", "Name", "Value", "Sort"}, {"ID 2", "Name", "Value", "Sort"}),
    Group = Table.Group(#"Expanded Count1", {"ID 2"}, {{"Count", each _, type table}}),
    #"Added Add.SurveyNo (index)" = Table.AddIndexColumn(Group, "Custom Column", 0, 1),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Add.SurveyNo (index)",{{"Custom Column", type text}}),
    #"Added Prefix" = Table.TransformColumns(#"Changed Type", {{"Custom Column", each "Survey " & _, type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Added Prefix",{"ID 2"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Columns","Survey 0",null,Replacer.ReplaceValue,{"Custom Column"}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Replaced Value", "Count", {"ID", "ID 2", "Name", "Sort", "Value"}, {"ID", "ID 2", "Name", "Sort", "Value"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Count",{{"ID", Order.Ascending}, {"Sort", Order.Ascending}}),
    #"Removed SortNo" = Table.RemoveColumns(#"Sorted Rows",{"Sort"})
in
    #"Removed SortNo"

[v-lili6-msft]

hi, @Ola_S

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?

 

Best Regards,
Lin

[tgjones43]

Hi @v-lili6-msft and @Ola_S. Thank you so much for your help. I have only just got round to trying the code. It works very well, but it doesn't quite match the expected outcome...

I have pasted a slightly longer view of my data below, this time with two values for [ID]. The main problem is that where [ID2] is null, the value in [Expected Outcome] should also be null, but as you can see in [Custom Column], it is displaying Survey 1. And it doing this for every null value in [ID2], no matter what the value of [ID].

Can you suggest a tweek to your solution?

Thank you.

ID	ID 2	Name	Value	Custom Column	Expected Outcome
21771061	null	Altitude -	84	Survey 1	null
21771061	null	Area -	South East	Survey 1	null
21771061	null	CEH 50K catchment id -	196541 - Severn Upper above Montfort C054001I	Survey 1	null
21771061	null	CEH 50k catchment id number	196541	Survey 1	null
21771061	21778928	Site purpose - First survey	01/01/2015	Survey 3	Survey 2
21771061	21778928	Site purpose - Guideline method	Electric Fishing	Survey 3	Survey 2
21771061	21776925	Site purpose - Guideline method	Electric Fishing	null	Survey 1
21771061	21777935	Site purpose - Guideline method	Electric Fishing	Survey 2	Survey 3
21771061	21777935	Site purpose - Program	Core Fisheries Monitoring Programme	Survey 2	Survey 3
21771061	21776925	Site purpose - Program	Core Fisheries Monitoring Programme	null	Survey 1
21771061	21778928	Site purpose - Program	Core Fisheries Monitoring Programme	Survey 3	Survey 2
21771061	21777935	Site purpose - Purpose	SAP / ST - Spatial	Survey 2	Survey 3
21771061	21778928	Site purpose - Purpose	SAP / ST - Spatial	Survey 3	Survey 2
21771061	21776925	Site purpose - Purpose	Coarse - Temporal	null	Survey 1
21771061	21777935	Site purpose - Repeat frequency	6 Years	Survey 2	Survey 3
21771061	21776925	Site purpose - Repeat frequency	1 Year	null	Survey 1
21771061	21778928	Site purpose - Repeat frequency	6 Years	Survey 3	Survey 2
21771061	21776925	Site purpose - Repeat from	01/01/2001	null	Survey 1
21771061	21778928	Site purpose - Repeat from	01/01/2012	Survey 3	Survey 2
21771061	21777935	Site purpose - Repeat from	01/01/2001	Survey 2	Survey 3
21771061	21776925	Site purpose - Repeat until	31/12/2006	null	Survey 1
21771061	21778928	Site purpose - Repeat until	31/12/2025	Survey 3	Survey 2
21771061	21777935	Site purpose - Repeat until	31/12/2006	Survey 2	Survey 3
21771061	21776925	Site purpose - Survey after	01/01/2001	null	Survey 1
21771061	21778928	Site purpose - Survey after	01/01/2012	Survey 3	Survey 2
21771061	21777935	Site purpose - Survey after	01/01/2001	Survey 2	Survey 3
21771061	21777935	Site purpose - Survey before	31/12/2001	Survey 2	Survey 3
21771061	21776925	Site purpose - Survey before	31/12/2001	null	Survey 1
21771061	21778928	Site purpose - Survey before	31/12/2012	Survey 3	Survey 2
21771061	21777935	Site purpose - Survey strategy	Qualitative	Survey 2	Survey 3
21771061	21778928	Site purpose - Survey strategy	CPUE/T	Survey 3	Survey 2
21771061	21776925	Site purpose - Survey strategy	Quantitative	null	Survey 1
21771061	21776925	Site purpose - Target guild	Coarse	null	Survey 1
21771061	21778928	Site purpose - Target guild	Salmon / Sea Trout	Survey 3	Survey 2
21771061	21777935	Site purpose - Target guild	Salmon / Sea Trout	Survey 2	Survey 3
21771062	null	Altitude -	82	Survey 1	null
21771062	null	Area -	South East	Survey 1	null
21771062	null	CEH 50K catchment id -	196541 - Severn Upper above Montfort C054001I	Survey 1	null
21771062	null	CEH 50k catchment id number	196541	Survey 1	null
21771062	21777936	Site purpose - First survey	01/01/2015	Survey 5	Survey 5
21771062	21777936	Site purpose - Guideline method	Electric Fishing	Survey 5	Survey 5
21771062	21776926	Site purpose - Guideline method	Electric Fishing	Survey 4	Survey 4
21771062	21777936	Site purpose - Program	Core Fisheries Monitoring Programme	Survey 5	Survey 5
21771062	21776926	Site purpose - Program	Core Fisheries Monitoring Programme	Survey 4	Survey 4
21771062	21776926	Site purpose - Purpose	Coarse - Temporal	Survey 4	Survey 4
21771062	21777936	Site purpose - Purpose	SAP / ST - Spatial	Survey 5	Survey 5
21771062	21776926	Site purpose - Repeat frequency	1 Year	Survey 4	Survey 4
21771062	21777936	Site purpose - Repeat frequency	6 Years	Survey 5	Survey 5
21771062	21777936	Site purpose - Repeat from	01/01/2001	Survey 5	Survey 5
21771062	21776926	Site purpose - Repeat from	01/01/2001	Survey 4	Survey 4
21771062	21777936	Site purpose - Repeat until	31/12/2025	Survey 5	Survey 5
21771062	21776926	Site purpose - Repeat until	31/12/2006	Survey 4	Survey 4
21771062	21776926	Site purpose - Survey after	01/01/2001	Survey 4	Survey 4
21771062	21777936	Site purpose - Survey after	01/08/2013	Survey 5	Survey 5
21771062	21777936	Site purpose - Survey before	31/10/2013	Survey 5	Survey 5
21771062	21776926	Site purpose - Survey before	31/12/2001	Survey 4	Survey 4
21771062	21776926	Site purpose - Survey strategy	Quantitative	Survey 4	Survey 4
21771062	21777936	Site purpose - Survey strategy	CPUE/T	Survey 5	Survey 5
21771062	21777936	Site purpose - Target guild	Salmon / Sea Trout	Survey 5	Survey 5
21771062	21776926	Site purpose - Target guild	Coarse	Survey 4	Survey 4

[v-lili6-msft]

hi, @tgjones43

What is your expected outcome Survey 1, Survey 2, Survey 3, Is it an index column for each ID and ID2?

You may try this way as below:

Step1:

Duplicate the basic query, then group by it as below:

Step2:

Filter null value for ID2 and remove Count column

Step3:

Add an index column by ID

= Table.Group( #"Filtered Rows", {"ID"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}})

and expand the column

Step4:

Merge the tables and expand the column

Step5:

Change the index column to text and add a custom column as below

=if [#"Table1 (2).Index"] = null then null else "Survey" &  [#"Table1 (2).Index"]

Result:

Here is pbix file, please try it.

Best Regards,

Lin

[tgjones43]

Hi @v-lili6-msft

Thank you, that looks great. But is there a way to do this without creating a second query? I would like to include the functions you have described after several steps and would prefer everything to be in one query. 

[v-lili6-msft]

HI, @tgjones43

Try this pbix file, Table1 (3) in Edit Queries.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZJBb8IwDIX/StQzk0hZyzhOMHZCYuskDohD1HpgLW2KmzD1389FY2iiRIFTnOi9T36x1+soluPxUKYyGkSV05qPZ23RugK4jJ8eT++bQZ92hYXd8ZlckXXlZChHXC7JbEmVXE0NgZhjswNCaMTCVGgNYbUVZ03m6ACtkL1AmST3AuNeYDxJ/wFXygKJOV/g29CXmCFBbvEAJ2AJZ+TIF9pRbZpO/AFlbUjpwGwenzfCny9TuuR/KML6fIcalBWfBHsHVd7ykxQtKAprN8Du67rHnh7tTVj3v5LMEs9t29nfnOIdUN3IwhJcIjIo8WHPHHsB8mW5CeQL9eqwAI0ViAXYnekGuZxNxYvmTSTMj/vOKx4W72aYL2IP7DqII25+AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, #"ID 2" = _t, Name = _t, Value = _t, #"Custom Column" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"ID 2", Int64.Type}, {"Name", type text}, {"Value", type text}, {"Custom Column", type text}}),
 Partition = Table.Group( #"Changed Type" , {"ID","ID 2"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Name", "Value", "Custom Column", "Index"}, {"Name", "Value", "Custom Column", "Index"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Partition",{{"Index", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each if [ID 2] = null then null else "Survey" &  [Index])
in
    #"Added Custom"

Best Regards,

Lin

[Greg_Deckler]

So, could you just create a second query where you pull out all of the distinct values from ID2 and assign an Index to them. That way you should be able to easily relate that dimension table back to this fact table and have what you want, correct?