none
Combine the Data from other excel sheets RRS feed

  • Question

  • Hello All,

    I have below data

    _RowNum Source.Name Column1 Column2
    0 test-1.xlsx Sl No 123
    1 test-1.xlsx Make ABC
    2 test-1.xlsx Year 1998
    3 test-2.xlsx Sl No 456
    4 test-2.xlsx Make dbes
    5 test-2.xlsx Year 2000
    6 test-3.xlsx Sl No 789
    7 test-3.xlsx Make dddds
    8 test-3.xlsx Year 1995

    I want them in a combined version like below (Not one by one rather grouped into the common names in column1)

    _RowNum Source.Name Column1 Column14 Column13 Column12
    0 test-1.xlsx Sl No 123 456 789
    1 test-1.xlsx Make ABC des ddds
    2 test-1.xlsx Year 1998 2000 1995

    Is it possible to get the output like this?

    Monday, July 8, 2019 8:47 AM

Answers

  • Maybe the following is waht you want:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table_combine"]}[Content],
        Group = Table.Group(Source, {"Column1"}, {{"content", each Record.FromList(_[Column2], type [test1, test2, test3])}}),
        Expand = Table.ExpandRecordColumn(Group, "content", {"test1", "test2", "test3"}, {"test1", "test2", "test3"})
    in #"Expand"

    Monday, July 8, 2019 10:37 AM

All replies

  • it is likely possible, just it is not clear what transformation you exactly want
    Monday, July 8, 2019 10:16 AM
  • Maybe the following is waht you want:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table_combine"]}[Content],
        Group = Table.Group(Source, {"Column1"}, {{"content", each Record.FromList(_[Column2], type [test1, test2, test3])}}),
        Expand = Table.ExpandRecordColumn(Group, "content", {"test1", "test2", "test3"}, {"test1", "test2", "test3"})
    in #"Expand"

    Monday, July 8, 2019 10:37 AM
  • Many thanks for your answer.

    Could you please help me how to use this logic?

    Is it just a formula to be entered or Visual Basic language?

    let
        Source = Excel.CurrentWorkbook(){[Name="Table_combine"]}[Content],
        Group = Table.Group(Source, {"Column1"}, {{"content", each Record.FromList(_[Column2], type [test1, test2, test3])}}),
        Expand = Table.ExpandRecordColumn(Group, "content", {"test1", "test2", "test3"}, {"test1", "test2", "test3"})
    in #"Expand"


    Regards, Jesudoss Savarimuthu

    Monday, July 29, 2019 1:07 PM
  • This is M language, aka Power Query. This is nothing related to vba.
    You can use it either in Power Bi or in Excel.

    To use it, you need to google it, you will find many video on you tube for beginners


    • Edited by anthony34 Monday, July 29, 2019 1:16 PM
    Monday, July 29, 2019 1:15 PM