Forum Discussion
Dynamically select and merge columns while retaining others
Hi Insert_Key
The problem comes from:
Select Label Columns = Table.SelectColumns(Source, #"List Label Columns")You don't need to do that. Instead, simply pass the list of columns to merge (List Label Columns) to the step where you merge the columns - line 8 below. Something like:
let Source = ..., SourceNames = Table.ColumnNames( Source ), ListLabelColumns = List.Select( SourceNames, each Text.StartsWith(_, "Labels") ), MergedLabelColumns = Table.CombineColumns( Source, ListLabelColumns, Combiner.CombineTextByDelimiter(" ", QuoteStyle.None), "MERGED LABELS" ), Result = if List.Count(ListLabelColumns) < 2 then Source else MergedLabelColumns in Result
That's not it all, Lorenzo.
I'm sorry if my lack of response seemed like I didn't appreciate your response. I was completely snowed under at work with multiple competing priorities, and couldn't get your solution to fully resolve in my real-world file - presumably due to a skills shortage on my end. I was embarrassed to ask for more help, and to be honest, didn't have the time to. I posted on the 11th, you replied on the 11th, and have been on two weeks of Annual Leave since. I return to work tomorrow, and couldn't help but peek at my Inbox while away - there's a month's worth of work queued up for me already.
I am sure that your solution would work - but as I couldn't get it to with my actual data (and didn't have time to test in a dummy environment) I didn't want to check the "Mark as best response" button. As soon as I have been able to make it work - either in test or in prod - I will definitely return to click that button. I understand why that is so important (especially in a community setting) and genuinely appreciate your response to my call for help.
Thanks again 🙂 and apologies again 🙁
Fair enough Insert_Key If you have challenge implementing, share a dummy representative workbook (PQ isn't easy when beginning)
Lorenzo - OK I'm back at work and now back to this task, specifically. Your solution worked perfectly; I'm not sure what I was doing wrong previously... all I remember is that I was in a mad rush at the time. I specified " ; " as the delimiter, which then caused a minor issue by populating delimiters for null values - a bit of Googling and I was able to amend your code suitably. Posting it here in case it helps anyone else in the future 🙂
let Source = Excel.CurrentWorkbook(){[Name = "CM_Jira_Export"]}[Content], SourceLabelColumnNames = Table.ColumnNames(Source), ListLabelColumns = List.Select(SourceLabelColumnNames, each Text.StartsWith(_, "Labels")), MergedLabelColumns = Table.CombineColumns( Source, ListLabelColumns, each Combiner.CombineTextByDelimiter(" ; ", QuoteStyle.None)(List.RemoveNulls(_)), "Labels" ), #"Created merged ""Label"" Column" = if List.Count(ListLabelColumns) < 2 then Source else MergedLabelColumns in #"Created merged ""Label"" Column"I repeated these steps for three other columns that are/can be duplicated in the export. It looks a bit messy, but it works. Now onto the next step: dynamically renaming headers that contain identical redundant text. I will make a new post, and upload/attach a sample file. Thanks again for your help.
Insert_Key Glad you have it working now & thanks for feedback
re. I repeated these steps for three other columns that are/can be duplicated in the export. It looks a bit messy, but it works
Difficult to say for sure without a realistic sample but repeating steps should be doable with https://learn.microsoft.com/en-us/powerquery-m/list-accumulate