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

Join 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.

Reply
Syndicate_Admin
Administrator
Administrator

Handle CSV files where the # of duplicate column names can change

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

1 ACCEPTED 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

View solution in original post

4 REPLIES 4
dmanning
Frequent Visitor

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.

AlexisOlson
Super User
Super User

What does the code for your merge step look like? Feel free to paste the M code for the whole query.

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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