locked
Converting row based id,value data into row based id,values as CSV list RRS feed

  • Question

  • Using Power Query, how can I convert the following data:

    |Key|Value|

    |A|1|

    |A|2|

    | B |2 |

    |B|3|

    |B|4|

    ... into the following.

    |Key|Values|

    |A|1,2|

    |B|2,3,4|

    where the number of 'key' and 'value' items are unknown or variable.

    If have tried adding a column using formula such as follows

    = Table.AddColumn(#"Transformed", "Values", each Text.Combine([Value], ", "))

    The full script looks like the following and results in a 3 column which does not expand.

    let   

    Source = Csv.Document(File.Contents("..."),[Delimiter=",",Encoding=1252]),   

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),

      #"Final" = Table.AddColumn(#"Changed Type", "Options", each Table.AddColumn(#"Changed Type", "column3", each Text.Combine([Column1][column2], ", ")))

    in

        Final


    I'm quite new to this so any help really appreciated.

    Thanks



    • Edited by naleo96 Friday, March 11, 2016 2:21 AM reformat question
    Friday, March 11, 2016 2:14 AM

Answers

  • Have you tried Table.Group? You can access to it by right clicking a column name in the query editor. Your M query should be like this:

    let
        Source = Csv.Document(File.Contents(path),[Delimiter=",", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]),
        #"Promoted Headers" = Table.PromoteHeaders(Source),
        #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"key", type text}, {"value", type text}}),
        #"Grouped Rows" = Table.Group(#"Changed Type", {"key"}, {{"AllRows", each _, type table}}),
        #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Text.Combine([AllRows][value], ", "))
    in
        #"Added Custom"

    for a CSV that looks like this:

    key,value
    A,1
    A,2
    B,2
    B,3
    B,4


    • Edited by Oguz Yildiz Friday, March 11, 2016 3:49 AM
    • Proposed as answer by Tristan St-Cyr Friday, March 11, 2016 8:01 PM
    • Marked as answer by naleo96 Tuesday, March 15, 2016 12:35 AM
    Friday, March 11, 2016 3:49 AM

All replies

  • Have you tried Table.Group? You can access to it by right clicking a column name in the query editor. Your M query should be like this:

    let
        Source = Csv.Document(File.Contents(path),[Delimiter=",", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]),
        #"Promoted Headers" = Table.PromoteHeaders(Source),
        #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"key", type text}, {"value", type text}}),
        #"Grouped Rows" = Table.Group(#"Changed Type", {"key"}, {{"AllRows", each _, type table}}),
        #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Text.Combine([AllRows][value], ", "))
    in
        #"Added Custom"

    for a CSV that looks like this:

    key,value
    A,1
    A,2
    B,2
    B,3
    B,4


    • Edited by Oguz Yildiz Friday, March 11, 2016 3:49 AM
    • Proposed as answer by Tristan St-Cyr Friday, March 11, 2016 8:01 PM
    • Marked as answer by naleo96 Tuesday, March 15, 2016 12:35 AM
    Friday, March 11, 2016 3:49 AM
  • Excellent. Thanks for you help. Now I see the steps it all makes sense. Thankyou.

    Tuesday, March 15, 2016 12:36 AM