cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

how to enable left join with mysql group_concat csv merged instances of right side column values

 

Any insights on how to address power bi power query matter outlined in this stackoverflow post?  

 

Note that i had to use stackoverflow because repeated attempts to post here kept messing with the text and code box formatting or erroring out complaining about html being embedded in text.

1 ACCEPTED SOLUTION
Super User I
Super User I

Hi @myusrn ,

Is this what you're looking for?

let
    Source = Table.NestedJoin(#"mysqldb support_request", {"id"}, #"mysqldb support_request_tag"[[#"support_request_id"], [#"tag_name"]], {"support_request_id"}, "tag_name_csv", JoinKind.LeftOuter),
    #"Transformed tags" = Table.TransformColumns(Source, {{"tag_name_csv", each Text.Combine(_[#"tag_name"], ", "), type text}})
in
    #"Transformed tags"



Feel free to connect with me:
LinkedIn

View solution in original post

5 REPLIES 5
Super User I
Super User I

Hi @myusrn ,

Is this what you're looking for?

let
    Source = Table.NestedJoin(#"mysqldb support_request", {"id"}, #"mysqldb support_request_tag"[[#"support_request_id"], [#"tag_name"]], {"support_request_id"}, "tag_name_csv", JoinKind.LeftOuter),
    #"Transformed tags" = Table.TransformColumns(Source, {{"tag_name_csv", each Text.Combine(_[#"tag_name"], ", "), type text}})
in
    #"Transformed tags"



Feel free to connect with me:
LinkedIn

View solution in original post

@Smauro that worked.  Thank you very much.  Leaves me wondering what scenario Table.AggregateTableColumn( . . . ) function is for when Table.TransformColumns( . . . ) covers this case.

Super User III
Super User III

Hi @myusrn 

Without seeing your source data and an example of your expected result it's difficult to envusion what you are trying to do.

Phil


If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

Super User III
Super User III

Hi @myusrn 

Can you supply the data tables/queries/PBIX file you are trying to merge and an example of the expected result.

But the general approach to joining tables in PBI/PQ is, in the Query Editor, Home Tab, click on Merge Queries -> Merge Queries As New then select the left and right tables, the type of join and the matching/joining column

join.png

Regards

Phil


If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

Thanks for response.  I'm not having any issues setting up left join merge query to new table output.  What i'm looking for is way to use #"Aggregate . . . " in lieu of #"Grouped Rows . . ." on column in right side of join to produce single row per left side where there is multiple right side matches and combining a specific column in the right side matches into a comma seperated value using Text.Combine(List.???, ", ") function.  This is detailed in the stackoverflow post referenced in the question overview.

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors