Forum Discussion

Bernie6808's avatar
Bernie6808
Frequent Visitor
2 years ago
Solved

Merge a changing list of columns

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

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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"

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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"