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

Anonymous's avatar
Anonymous
Not applicable
7 years ago

hello tgjones43 

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

 

tgjones43's avatar
tgjones43
Helper IV
7 years ago

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's avatar
    Anonymous
    Not applicable
    7 years ago

    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