Merge a changing list of columns

[Bernie6808]

Hi Folks,

I have a set of data with two columns: Project Number and Fund Source. There can be more than one Fund Source per Project and I want to group the data so there's a single row per project with Funders separated by pipes.

My code below uses a 'merge queries' to bring Funder_Name in based on the Funder_ID, and then creates a new column 'Funder_Name_2 with the Funder name within pipes. I then pivot the data to put the funder name into columns, and the 'piped' Funder_Name_2 goes into 'Values'

Source = #"WT Database",
    dbo_Funder_per_Project_Funder = Source{[Schema="dbo",Item="Funder_per_Project_Funder"]}[Data],
    #"Merged Queries" = Table.NestedJoin(dbo_Funder_per_Project_Funder, {"Funder_ID"}, #"WT Funder", {"Funder_ID"}, "WT Funder", JoinKind.LeftOuter),
    #"Expanded WT Funder" = Table.ExpandTableColumn(#"Merged Queries", "WT Funder", {"Name"}, {"WT Funder.Name"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded WT Funder",{"Funder_ID"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"WT Funder.Name", "Funder_Name"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Funder_Name_2", each "|" & [Funder_Name] &"|"),
    #"Pivoted Column" = Table.Pivot(#"Added Custom", List.Distinct(#"Added Custom"[Funder_Name]), "Funder_Name", "Funder_Name_2"),

The next line of code (not included above) is

#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Pivoted Column", ....

where ... represents a huge list of every funder in the system (thousands of them). Each time a funder is added to the system, the query fails because the new Funder is not in the list of columns to merge.

I either want:

a) a dynamic solution which replaces the list of columns to merge with something along the lines of 'Merge every column apart from the Project Number'  

b) a more elegant solution overall!

P.S. the final bit of code replaces "||" with "|".

Any help appreciated

[Anonymous · Accepted solution]

Hi,

You can try adapting something like this to handle the dynamic merge. I've used same basic sample data as below.

Query 1 raw data

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRcgRiJyB2BmIXpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ProjectNum = _t, Funder_1 = _t, Funder_2 = _t, Funder_3 = _t, Funder_4 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ProjectNum", Int64.Type}, {"Funder_1", type text}, {"Funder_2", type text}, {"Funder_3", type text}, {"Funder_4", type text}})
in
#"Changed Type"

Query 2 get dynamic list of column names <> ProjectNum

let
Source = Table.ColumnNames(Source),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Filtered Rows" = Table.SelectRows(#"Converted to Table", each ([Column1] <> "ProjectNum")),
Column1 = #"Filtered Rows"[Column1]
in
Column1

Query 3 merge using Query 2

let
Source = Source,
#"Merged Columns" = Table.CombineColumns(Source, ColumnNames, Combiner.CombineTextByDelimiter(",", QuoteStyle.None), "Funder" as text)
in
#"Merged Columns"