Results 1 to 1 of 1

Thread: PQ - Grouping rows with different data type

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. 07-31-2023, 01:19 AM #1
    sandy666
    sandy666 is offline
    Sir Moderator sandy666's Avatar
    Join Date
    May 2018
    Posts
    238
    Rep Power
    7

    Cool PQ - Grouping rows with different data type

    Source Result
    ToGroup Result ToGroup Result
    A
    20
    		 A
    40
    B B
    C
    15
    		 C
    15
    A
    20
    B

    ToGroup Result ToGroup Result
    A
    20
    		 A
    40
    B
    100
    		 B
    100
    C
    15
    		 C
    15
    A
    20
    B

    ToGroup Result ToGroup Result
    A
    20
    		 A
    40
    B
    5
    		 B
    15
    C
    15
    		 C
    15
    A
    20
    B
    10

    ToGroup Result ToGroup Result
    A
    20
    		 A
    40
    B John B MixedDataType
    C
    15
    		 C
    15
    A
    20
    B
    10

    ToGroup Result ToGroup Result
    A
    20
    		 A
    40
    B John B John, Mark
    C
    15
    		 C
    15
    A
    20
    B Mark

    Code:
    // Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"ToGroup"}, {{"Result", each try try List.Sum([Result]) otherwise Text.Combine(List.Distinct([Result]), ", ") otherwise "MixedDataType", type any}})
in
    Group
    Last edited by sandy666; 07-31-2023 at 01:24 AM.
    sandy
    I know you know but I forgot my Crystal Ball and don't know what you know also I may not remember what I did weeks, months or years ago so answer asap. Thinking doesn't hurt
« Previous Thread | Next Thread »

Similar Threads

  1. PQ - Grouping columns in rows and get data marked by x together
    By sandy666 in forum ETL PQ Tips and Tricks
    Replies: 1
    Last Post: 07-26-2023, 01:43 PM
  2. Dynamic Arrays/ Spilling alternative to type 2 CSE Entry
    By DocAElstein in forum Public News
    Replies: 0
    Last Post: 02-23-2019, 03:18 PM
  3. Why is this code returning a Runtime error 13 Type mismatch?
    By KennyA in forum Excel Help
    Replies: 8
    Last Post: 09-01-2015, 01:50 AM
  4. Merging And Grouping Rows And Corresponding Data In Table That Meet Specific Criteria
    By aaronb in forum Excel Help
    Replies: 3
    Last Post: 08-18-2013, 08:59 PM
  5. Passing a UDF as return type
    By Junoon in forum Excel Help
    Replies: 0
    Last Post: 05-05-2012, 05:26 PM

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
  •  

Forum Rules