I am trying to merge queries while concatenating cell and columns data

[jburbano]

This is my data:

FullName	Campaign	IdeaId	Question	Response
Al Michaels	White space theme	FA75E29A-C71C-ED11-B83E-000D3A55D9D3	What is the challenge or opportunity to be solved?	Test Answer 1
Al Michaels	White space theme	FA75E29A-C71C-ED11-B83E-000D3A55D9D3	What is your idea as to a possible solution?  This could be a specific solution or a general idea.	Test Answer 2
Al Michaels	White space theme	FA75E29A-C71C-ED11-B83E-000D3A55D9D3	What are the benefits of your solution to our teammates, the organization, customers?	Test Answer 3
Joel Phillipe	White space theme	A9ECC1E0-A51C-ED11-B83E-000D3A55D9D3	What is the challenge or opportunity to be solved?	test
Joel Phillipe	White space theme	A9ECC1E0-A51C-ED11-B83E-000D3A55D9D3	What is your idea as to a possible solution?  This could be a specific solution or a general idea.	test
Joel Phillipe	White space theme	A9ECC1E0-A51C-ED11-B83E-000D3A55D9D3	What are the benefits of your solution to our teammates, the organization, customers?	test
Joel Phillipe	White space theme	91419FA6-CB1C-ED11-B83E-000D3A55D9D3	What is the challenge or opportunity to be solved?	Test2
Joel Phillipe	White space theme	91419FA6-CB1C-ED11-B83E-000D3A55D9D3	What is your idea as to a possible solution?  This could be a specific solution or a general idea.	Test2
Joel Phillipe	White space theme	91419FA6-CB1C-ED11-B83E-000D3A55D9D3	What are the benefits of your solution to our teammates, the organization, customers?	Test2
Joel Phillipe	External theme	7478AFB2-CB1C-ED11-B83E-000D3A55D9D3	What is the challenge or opportunity to be solved?	Test 3
Joel Phillipe	External theme	7478AFB2-CB1C-ED11-B83E-000D3A55D9D3	What is your idea as to a possible solution?  This could be a specific solution or a general idea.	Test 3
Joel Phillipe	External theme	7478AFB2-CB1C-ED11-B83E-000D3A55D9D3	What are the benefits of your solution to our teammates, the organization, customers?	Test 3
Joel Phillipe	Internal theme	7494C8C9-CB1C-ED11-B83E-000D3A55D9D3	What is the challenge or opportunity to be solved?	Test 4
Joel Phillipe	Internal theme	7494C8C9-CB1C-ED11-B83E-000D3A55D9D3	What is your idea as to a possible solution?  This could be a specific solution or a general idea.	Test 4
Joel Phillipe	Internal theme	7494C8C9-CB1C-ED11-B83E-000D3A55D9D3	What are the benefits of your solution to our teammates, the organization, customers?	Test 4

What I am trying to do is combine all the data by FullName and pivot it to new columns and Concat seperate IdeaIds, like this:

User	External Theme - What is the challenge or opportunity to be solved?	External Theme - What is your idea as to a possible solution?  This could be a specific solution or a general idea.	External Theme - What are the benefits of your solution to our teammates, the organization, customers?	Internal Theme - What is the challenge or opportunity to be solved?	Internal Theme - What is your idea as to a possible solution?  This could be a specific solution or a general idea.	Internal Theme - What are the benefits of your solution to our teammates, the organization, customers?	White Space - What is the challenge or opportunity to be solved?	White Space - What is your idea as to a possible solution?  This could be a specific solution or a general idea.	White Space - What are the benefits of your solution to our teammates, the organization, customers?
Al Michaels							Idea 1 - Test Answer 1	Idea 1 - Test Answer 2	Idea 1 - Test Answer 3
Joel Phillipe	Idea 1 - Test 3 Idea 2 - Test 4	Idea 1 - Test 3 Idea 2 - Test 4	Idea 1 - Test 3 Idea 2 - Test 4				Idea 1 - test Idea 2 - Test2	Idea 1 - test Idea 2 - Test2	Idea 1 - test Idea 2 - Test2

I've tried grouping, merge queries, etc.. but still not managing to get it as I'd like.

[jbwtp]

Hi @jburbano,

I am not sure if I understandcorretctly how the Ideas a numbered, but the solution can be somewhere along these lines:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("vZTBbsIwDIZfxeJMp5bCSk8otEXapEk7IO2AOIRiaKQ0qZJ0G3sanoUnW9pJHBAcps0cY9nO9+ePvVoNmIQXUVYcpR0MB2+VcAi24SWCq7BGH1uwZFKMUhZkSZQFRR5FwXwaF0EYhnnMJpM8zeO+lDsQtisD309KVHsEbUA3jTauVcIdwGnY+P5avuN25ouWaB0wZT/QQDRYDwl4Dro1ILbIgdvufg6NtlZsZM/ROqHV7HSEZeVzS93KbUfI/Z1Yip0oz0mdFA57VGi47Bs+XAgYEQjgpq/zTAp3wlnQux9FZywvqTs75HXNHdphn6/NnivxxbuUIZStdbpGYy/fPO6RnzVKeK2ElKLBG9AsLbIsKsKATf79F3hqRwVCaz8ZOYHvv2JNo3GULthjkM1Jhn5ERUI/7jToRIN+Hbb4dGiU19WTno4+lIyTKVvMR1SG31g1fye5w34nQafa7Ndhn9QV2HScTbOUzPExEckdHCdBp3Lcw66/AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FullName = _t, Campaign = _t, IdeaId = _t, Question = _t, Response = _t]),
    Main = Table.TransformColumnTypes(Source,{{"FullName", type text}, {"Campaign", type text}, {"IdeaId", type text}, {"Question", type text}, {"Response", type text}}),
    Ideas = 
        let 
            #"Removed Other Columns1" = Table.SelectColumns(Main,{"IdeaId"}),
            #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns1"),
            #"Added Index" = Table.AddIndexColumn(#"Removed Duplicates", "Index", 1, 1, Int64.Type),
            #"Added Custom" = Table.AddColumn(#"Added Index", "Ideas", each "Idea " & Text.From([Index]), type text),
            #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"IdeaId", "Ideas"})
        in
            #"Removed Other Columns",
    #"Merged Queries" = Table.NestedJoin(Main, {"IdeaId"}, Ideas, {"IdeaId"}, "Ideas.1", JoinKind.LeftOuter),
    #"Expanded Ideas.1" = Table.ExpandTableColumn(#"Merged Queries", "Ideas.1", {"Ideas"}, {"Ideas"}),
    #"Merged Columns" = Table.CombineColumns(#"Expanded Ideas.1",{"Ideas", "Response"},Combiner.CombineTextByDelimiter(" - ", QuoteStyle.None),"Merged"),
    #"Merged Columns1" = Table.CombineColumns(#"Merged Columns",{"Campaign", "Question"},Combiner.CombineTextByDelimiter(" - ", QuoteStyle.None),"Merged.1"),
    #"Grouped Rows" = Table.Group(#"Merged Columns1", {"FullName", "IdeaId"}, {{"Count", each Table.Pivot(_, List.Distinct(_[Merged.1]), "Merged.1", "Merged", List.First)}}),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"FullName", "IdeaId"}),
    Custom1 = Table.Combine(#"Removed Columns"[Count])
in
    Custom1

Let me know more detail about the conventions made in the project, if you want a more specific solution.

Kind regards,

John

[jburbano]

I am still looking to combine by user, in essence, one row per user, but concat the different ideas. I know can merge the responses ones more and for delimiter use newline; might show up weird in visuals, but will have to set them with text wrap.

[jbwtp]

Hi @jburbano,

I think this produces exact same view as you provided in your post:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("vZTBbsIwDIZfxeJMp5bCSk8otEXapEk7IO2AOIRiaKQ0qZJ0G3sanoUnW9pJHBAcps0cY9nO9+ePvVoNmIQXUVYcpR0MB2+VcAi24SWCq7BGH1uwZFKMUhZkSZQFRR5FwXwaF0EYhnnMJpM8zeO+lDsQtisD309KVHsEbUA3jTauVcIdwGnY+P5avuN25ouWaB0wZT/QQDRYDwl4Dro1ILbIgdvufg6NtlZsZM/ROqHV7HSEZeVzS93KbUfI/Z1Yip0oz0mdFA57VGi47Bs+XAgYEQjgpq/zTAp3wlnQux9FZywvqTs75HXNHdphn6/NnivxxbuUIZStdbpGYy/fPO6RnzVKeK2ElKLBG9AsLbIsKsKATf79F3hqRwVCaz8ZOYHvv2JNo3GULthjkM1Jhn5ERUI/7jToRIN+Hbb4dGiU19WTno4+lIyTKVvMR1SG31g1fye5w34nQafa7Ndhn9QV2HScTbOUzPExEckdHCdBp3Lcw66/AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FullName = _t, Campaign = _t, IdeaId = _t, Question = _t, Response = _t]),
    Main = Table.TransformColumnTypes(Source,{{"FullName", type text}, {"Campaign", type text}, {"IdeaId", type text}, {"Question", type text}, {"Response", type text}}),
    
    fCombine = (t as table) as table =>
        let 
            Ideas = 
                let 
                    #"Removed Duplicates" = Table.Distinct(t, {"IdeaId"}),
                    #"Removed Other Columns" = Table.SelectColumns(#"Removed Duplicates",{"IdeaId"}),
                    #"Added Index" = Table.AddIndexColumn(#"Removed Other Columns", "Index", 1, 1, Int64.Type)
                in
                    #"Added Index",
            #"Merged Queries" = Table.NestedJoin(t, {"IdeaId"}, Ideas, {"IdeaId"}, "next", JoinKind.LeftOuter),
            #"Expanded next" = Table.ExpandTableColumn(#"Merged Queries", "next", {"Index"}, {"Index"}),
            #"Merged Columns" = Table.CombineColumns(#"Expanded next",{"Campaign", "Question"},Combiner.CombineTextByDelimiter(" - ", QuoteStyle.None),"Merged"),
            #"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns", {{"Index", type text}}, "en-NZ"),{"Index", "Response"},(x) as text=>"Idea " &Text.Combine(x, " - "),"Merged.1"),
            #"Removed Other Columns" = Table.SelectColumns(#"Merged Columns1",{"FullName", "Merged", "Merged.1"}),
            #"Pivoted Column" = Table.Pivot(#"Removed Other Columns", List.Distinct(#"Removed Other Columns"[Merged]), "Merged", "Merged.1", each Text.Combine(_, "
"))
        in
            #"Pivoted Column",
    
    #"Grouped Rows" = Table.Group(Main, {"FullName"}, {{"Count", fCombine}}),
    Output = Table.Combine(#"Grouped Rows"[Count])
in
    Output

Cheers,

John