Forum Discussion

tgjones43's avatar
tgjones43
Helper IV
7 years ago
Solved

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 n...
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    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

v-frfei-msft's avatar
v-frfei-msft
Community Support
7 years ago

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's avatar
tgjones43
Helper IV
7 years ago

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