Results 1 to 13 of 13

Power Query: transpose data related to same ID in row

Threaded View

  1. #3
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Power Query: transpose data related to same ID in row

    Product Article ID EAN.1 EAN.2
    55a62da4-4f82-4fc4-afbc-c3fbdc40e1d2 8591454005939
    b04fa022-79bc-41ae-860b-b81cf33385b9 5703538244988
    d9e60527-9dff-4982-ad3f-1630ffa30386 5703538245244
    f596abca-e6bf-4527-b687-476a16565017 5713571000205 5713571000212
    517e03c6-432f-4bd8-9796-e9974f2f8b05 5703538759819
    e7176d60-a687-4f06-aa94-114e4b355e0b 5703538759499 5703538755903
    6b2723e9-b36f-457e-8b39-9ffac835be7d 5703538759994 5703538759970
    c9182c83-761d-4380-a867-42744ef5930d 5703538759697
    ae87520b-2a1c-443f-a8cc-2c07a82c2652 5703538759215
    51fc53ea-5286-4591-abf8-170c7559a6c1 5713571001219 5713571001202


    // Table1
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Text = Table.TransformColumnTypes(Source,{{"Product Article ID", type text}, {"EAN", type text}}),
        Grp = Table.Group(Text, {"Product Article ID"}, {{"All", each _, type table}}),
        List = Table.AddColumn(Grp, "EAN", each [All][EAN]),
        Ext = Table.TransformColumns(List, {"EAN", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
        MaxCount = List.Max(Table.AddColumn(Ext, "SCount", each List.Count(Text.Split([EAN],",")))[SCount]),
        Split = Table.SplitColumn(Ext, "EAN", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), MaxCount )
    in
        Split
    do not use automatic Change Type
    Last edited by sandy666; 04-17-2023 at 01:18 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Transpose List in power query
    By JyothiGrace in forum Excel General
    Replies: 2
    Last Post: 07-01-2022, 08:45 AM
  2. Replies: 2
    Last Post: 11-12-2021, 03:44 AM
  3. Power Query grouping of related items to find earliest datetime
    By Marbleking in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-08-2021, 03:44 AM
  4. Power Query - Transpose Specific Columns
    By keith6292 in forum Excel General
    Replies: 1
    Last Post: 07-20-2021, 04:01 PM
  5. [SOLVED] Transpose values Power Query
    By JyothiGrace in forum Excel General
    Replies: 2
    Last Post: 07-19-2021, 08:48 AM
  6. Replies: 10
    Last Post: 06-25-2020, 12:19 AM
  7. Need a solution for power query related work
    By jones143 in forum Excel General
    Replies: 0
    Last Post: 05-16-2020, 02:32 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1