Forum Discussion
Helper IVCustom column based on several other columns
Helper IVThank 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 |
Community Supporthi, 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