Forum Discussion
Add Column query
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
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 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