Forum Discussion
Custom column based on several other columns
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 |
...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-msft7 years ago
Community Support
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- tgjones437 years ago
Helper IV
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