Forum Discussion
Add Column query
Hi all
I have the following two columns and would like to create the third column, in the Query Editor. Each value in Column A occupies 1 or more rows. The values in Column B are random 8 digit numbers which are not necessarily in ascending numerical order (e.g. the value for row 2 is smaller than the value for row 1, however, where a value in Column A occupies more than one row (e.g. value 3), the corresponding values in column B are in ascending numerical order.
I assume there is probably a fairly simple solution to this, without using Column B. Thank you!
| Column A | Column B | Required Column |
| 1 | 21770356 | 1 |
| 2 | 21770328 | 1 |
| 3 | 21770325 | 1 |
| 3 | 21770345 | 2 |
| 3 | 21770346 | 3 |
| 4 | 21770355 | 1 |
| 4 | 21770357 | 2 |
| 5 | 21770329 | 1 |
| 5 | 21770330 | 2 |
| 5 | 21770390 | 3 |
Hi tgjones43 ,
We can insert index by catgoary by this way. Please refer to the M code as below.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc3LDcAwCAPQXXzOgU8ozSwo+6/RppWgqnJDTzaOAKNB2J3UjvtkzBaQRDkTtdA22BfKH9dPfbDXUNU/6Fm3GhqZLFTaJAe9Q/MC", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Column A" = _t, #"Column B" = _t, #"Required Column" = _t]), Partition = Table.Group(Source, {"Column A"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}), #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Index"}, {"Partition.Index"}) in #"Expanded Partition"Please find the pbix as attached.
Regards,
Frank
9 Replies
hello tgjones43
what is your third column "Required column " for. is it a calculated or data column.
Hi Anonymous just a data column. I am going to merge it with another column that contains the word 'Survey', so that the column will say Survey 1, Survey 2, etc.
so you want column b to be compleatly random? how did you enter in data initialy?
Basicaly what is happening is the data is asociation the column B values with the repeated numbers, you need something to distinguish the repeated numbers in column A i would try creating an index column see if that does anything .
best regards,
Collin
Hi tgjones43 ,
We can insert index by catgoary by this way. Please refer to the M code as below.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc3LDcAwCAPQXXzOgU8ozSwo+6/RppWgqnJDTzaOAKNB2J3UjvtkzBaQRDkTtdA22BfKH9dPfbDXUNU/6Fm3GhqZLFTaJAe9Q/MC", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Column A" = _t, #"Column B" = _t, #"Required Column" = _t]), Partition = Table.Group(Source, {"Column A"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}), #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Index"}, {"Partition.Index"}) in #"Expanded Partition"Please find the pbix as attached.
Regards,
Frank
Thanks v-frfei-msft, that helps a lot.
I wonder if you would be able to propose a solution to a slightly more complex indexing query.
I have the following 2 columns and require the third. Each value in column A occupies several rows due to having several different values in column B. I want the required column to basically count each occurence of the word 'Purpose' in column B in order for each value of column A. And the final step of the M code needs to bring back all rows and columns (my dataset is much bigger than the example I am presenting). Is this possible?
Thank you!
Column A Column B Required Column 1 Altitude 1 Slope 1 Purpose 1 2 Altitude 2 Slope 2 Purpose 1 2 Purpose 2 3 Altitude 3 Slope 3 Purpose 1 3 Purpose 2 3 Purpose 3 It might help that in my dataset there is a third column (Column C) that provides an 8 digit number for all occurences of the word 'Purpose' in Column B:
Column A Column B Column C Required Column 1 Altitude null null 1 Slope null null 1 Purpose 21770356 1 2 Altitude null null 2 Slope null null 2 Purpose 21770325 1 2 Purpose 21770345 2 3 Altitude null null 3 Slope null null 3 Purpose 21770329 1 3 Purpose 21770330 2 3 Purpose 21770390 3
