Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
I have a CSV file that can contain duplicate column names, which are automatically sequenced when brought into the Query.
eg. CSV file has:
Key
Label
Label
Component
Component
Component
Loaded with column names:
Key
Label
Label_1
Label_2
Component
Component_3
Component_4
The sequence number can change whenever additional columns are added. For instance, if a fourthLabel column is on the file, the load will impact both Label and COmponent column names, e.g.
Key
Label
Label_1
Label_2
Label_3
Component
Component_4
Component_5
I figured out how to merge the columns in the query, but whenever additional columns are added I have to go into the Advanced Editor and add the new column and rename other columns.
I am wondering if there is a way to set up logic to loop thru the column names and include all columns with the same starting column name into one merged column.
So in the initial example:
Merged Labels would include Label, Label_1, Label_2
Merged Components would include Component, Component_3, Component_4
Then when a fourth Label column shows on the file,
Merged Labels would include Label, Label_1, Label_2, Label_3
Merged Components would include Component, Component_4, Component_5
Any ideas?
Thanks
Solved! Go to Solution.
Hope this helps...
let
Source = Table.FromColumns ( {{1..10}, {11..20}, {21..30}, {31..40}, {41..50}}, {"Label", "Label1", "Label2", "Component", "Component1"}),
Labels = List.Select( Table.ColumnNames(Source), each Text.Contains(_, "Label") ),
Comps = List.Select( Table.ColumnNames(Source), each Text.Contains(_, "Component") ),
ToText = Table.TransformColumnTypes( Source, List.Transform( Table.ColumnNames(Source), each {_, type text})),
MergedLabels = Table.CombineColumns(ToText,Labels,Combiner.CombineTextByDelimiter("~", QuoteStyle.None),"Merged Labels"),
MergedComps = Table.CombineColumns(MergedLabels,Comps,Combiner.CombineTextByDelimiter("~", QuoteStyle.None),"Merged Components")
in
MergedComps
Here is the full query. The lines in red are the ones I have to modify whenever the # of duplicate columns names change.
let
Source = Csv.Document(File.Contents(DataFolder2 & "\DSS_JIRA_Vitech.csv"),[Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.Csv]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
Output=Table.RenameColumns(#"Promoted Headers",List.Transform(Table.ColumnNames(#"Promoted Headers"), each {_, Text.Replace(_,"/","")})),
#"Renamed Columns" = Table.RenameColumns(#"Output",{{"Custom field (Story Points)", "Story Points"}
, {"Custom field (Drop Date)", "Drop Date"},{"Custom field (Epic Link)", "Feature Link"}
,{"Custom field (Epic Name)", "Feature Name"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Story Points", Int64.Type}}),
#"Sprint-Current: added" = Table.AddColumn(#"Changed Type", "Sprint-Current", each if [Sprint] = "" then "Unassigned" else [Sprint]),
#"Label: merge columns" = Table.AddColumn(#"Sprint-Current: added","Merged Labels"
, each Text.Combine(List.Select({[Labels], [Labels_1], [Labels_2], [Labels_3], [Labels_4], [Labels_5], [Labels_6]}, each _<> "" and _ <> null),"~")),
#"Component: merge columns" = Table.AddColumn(#"Label: merge columns","Merged Components"
, each Text.Combine(List.Select({[Components], [Components_11], [Components_12], [Components_13]}, each _<> "" and _ <> null),"~")),
#"Label-Primary: added" = Table.AddColumn(#"Component: merge columns", "Label-Primary",
each if Text.PositionOf([Merged Components],"MSS")>-1 then "MSS"
else if Text.PositionOf([Merged Components],"ESS")>-1 then "ESS"
else if Text.PositionOf([Merged Components],"BSS")>-1 then "BSS"
else if Text.PositionOf([Merged Components],"SalesCenter")>-1 then "BSS"
else if [Merged Components] = "" then "Unassigned"
else "Unknown"),
#"JIRA Source: added" = Table.AddColumn(#"Label-Primary: added", "JIRA Source", each "Vitech"),
#"Story Points: null to zero" = Table.ReplaceValue(#"JIRA Source: added",null,0,Replacer.ReplaceValue,{"Story Points"}),
#"Removed Other Columns" = Table.SelectColumns(#"Story Points: null to zero",{"Issue Type", "Issue key", "Summary", "Status", "Story Points", "Sprint-Current", "Label-Primary", "Feature Link", "Feature Name", "Created", "Updated", "Resolved", "Resolution", "JIRA Source","Merged Labels","Merged Components"})
in
#"Removed Other Columns"
Hope this helps...
let
Source = Table.FromColumns ( {{1..10}, {11..20}, {21..30}, {31..40}, {41..50}}, {"Label", "Label1", "Label2", "Component", "Component1"}),
Labels = List.Select( Table.ColumnNames(Source), each Text.Contains(_, "Label") ),
Comps = List.Select( Table.ColumnNames(Source), each Text.Contains(_, "Component") ),
ToText = Table.TransformColumnTypes( Source, List.Transform( Table.ColumnNames(Source), each {_, type text})),
MergedLabels = Table.CombineColumns(ToText,Labels,Combiner.CombineTextByDelimiter("~", QuoteStyle.None),"Merged Labels"),
MergedComps = Table.CombineColumns(MergedLabels,Comps,Combiner.CombineTextByDelimiter("~", QuoteStyle.None),"Merged Components")
in
MergedComps
This did help. I had to do some additional work because I needed to remove the extra delimiters generated for when the columns had no value, but I ended up with something that provided the desired results.
Thanks for your support.
What does the code for your merge step look like? Feel free to paste the M code for the whole query.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
9 | |
9 | |
7 | |
6 | |
6 |