Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Bernie6808
Frequent Visitor

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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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"

View solution in original post

1 REPLY 1
Anonymous
Not applicable

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"

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors