Add Column query

[tgjones43]

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

[v-frfei-msft · Accepted solution]

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

[tgjones43]

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

[tgjones43]

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

[v-frfei-msft]

Hi @tgjones43 ,

Please check the following steps as below.

1. Merge column A and column B in Table 1.

2. Duplicate table1 and add index in the duplicated table. (Table (2)).

3.Merge the two tables to get the result as we need, in the merged table, we should do some operations to get the result as we need. Please check the M code for the three tables.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXLMKcksKU1JBTIVlGJ1IILBOfkFqCIBpUUF+cUgMUOwmBE2rUYYWo1waEWIGYHFjLEZZ4xhnDEW44xxGIcQM1aKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Column A" = _t, #"Column B" = _t, #"Required Column" = _t]),
    #"Removed Columns" = Table.RemoveColumns(Source,{"Required Column"}),
    #"Merged Columns" = Table.CombineColumns(#"Removed Columns",{"Column A", "Column B"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged")
in
    #"Merged Columns"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXLMKcksKU1JBTIVlGJ1IILBOfkFqCIBpUUF+cUgMUOwmBE2rUYYWo1waEWIGYHFjLEZZ4xhnDEW44xxGIcQM1aKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Column A" = _t, #"Column B" = _t, #"Required Column" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column A", Int64.Type}, {"Column B", type text}, {"Required Column", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column B] = "Purpose")),
Partition = Table.Group(#"Filtered Rows", {"Column A"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Column B", "Index"}, {"Partition.Column B", "Partition.Index"}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Expanded Partition", {{"Column A", type text}}, "en-US"),{"Column A", "Partition.Column B"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged")
in
    #"Merged Columns"

let
    Source = Table.NestedJoin(Table1,{"Merged"},#"Table1 (2)",{"Merged"},"Table1 (2)",JoinKind.LeftOuter),
    #"Expanded Table1 (2)" = Table.ExpandTableColumn(Source, "Table1 (2)", {"Partition.Index"}, {"Table1 (2).Partition.Index"}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Expanded Table1 (2)", {{"Table1 (2).Partition.Index", type text}}, "en-US"),{"Table1 (2).Partition.Index", "Merged"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged.1"),
    #"Removed Duplicates" = Table.Distinct(#"Merged Columns"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Duplicates", "Merged.1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Merged.1.1", "Merged.1.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Merged.1.1", Int64.Type}, {"Merged.1.2", type text}}),
    #"Split Column by Position" = Table.SplitColumn(#"Changed Type", "Merged.1.2", Splitter.SplitTextByPositions({0, 1}, false), {"Merged.1.2.1", "Merged.1.2.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Merged.1.2.1", Int64.Type}, {"Merged.1.2.2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Merged.1.1", "Required Column"}, {"Merged.1.2.1", "Column A"}, {"Merged.1.2.2", "Column B"}})
in
    #"Renamed Columns"

For more details, please check the pbix as attached.

Regards,

Frank

[v-frfei-msft]

Hi @tgjones43 ,

Does that make sense? If so, kindly mark my answer as the solution to close the case please. Thanks in advance.

Regards,
Frank

[tgjones43]

Thank you so much @v-frfei-msft, that is a great solution.

[Anonymous]

hello @tgjones43 

what is your third column "Required column " for. is it a calculated or data column.

[tgjones43]

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.

[Anonymous]

@tgjones43 

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