Forum Discussion
Custom column based on several other columns
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!
9 Replies
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
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 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?
