Forum Discussion
Power Query - removing identical generic text from the start and end of multiple columns
Hi Insert_Key
Thanks for sharing all what you shared and TBH I wouldn't have bet on GenAI regarding data types. What they came up with is very similar to what I've seen so far and what I referred to in my previous reply. Though, AI got it wrong when it comes to numbers vs. whole numbers... In the workbook you shared - just for testing purpose - enter a decimal number in column [Parent id] then refresh the query... 😒
In attached v1.2 I fixed it as follow:
DetectDataType = (column as list) as type => if List.IsEmpty(column) then type nullable text // Default to text if empty else let sampleValue = List.First(List.RemoveNulls(column)), // Get the first non-null value valueType = Value.Type(sampleValue) in if valueType = type text then type nullable text else if valueType = type number and (sampleValue = Number.Round(sampleValue)) then type nullable Int64.Type // Check for whole numbers else if valueType = type number then type nullable number // Decimal numbers else if valueType = type logical then type nullable logical else if valueType = type date or valueType = type datetime then type nullable datetime // Handle Date/Time else type nullable text, // Fallback to textand IMHO the last else should be type nullable any and not text but that doesn't matter in your context as any will end as text once the query loads to the Data Model
A comment, just in case... I see you added a Sort step at the end of your query. I guess that's for checks on the query output within Excel. Otherwise, as far as I know, the Data Model doesn't mind about ordered data
Last thing. Now that I understand your solution has no external connection, you can slightly speed up the time to refresh your query by disabling the PQ Firewall. Excel data (tab) > Get Data > Query Options... > CURRENT WORKBOOK > Privacy > Choose Ignore the Privacy Levels...
Hope this helps and makes sense
Attached Query3 merges the Label & Custom fields in 1 step with List.Accumulate:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
SourceLabelColumnNames = Table.ColumnNames( Source ),
TransformedList = List.Transform(SourceLabelColumnNames, each
if Text.Contains(_, "(" ) then Text.BetweenDelimiters(_, "(", ")" )
else try Text.Range(_, 0, Text.PositionOfAny(_, {"0".."9"} ) ) otherwise _
),
DistinctList = List.Distinct( TransformedList ),
ColumnsToMerge = List.Distinct( List.Difference( TransformedList, DistinctList ) ),
MergedColumns = List.Accumulate( ColumnsToMerge, Source,
(accum,name) as table =>
Table.CombineColumns(
accum,
List.Select( SourceLabelColumnNames, each Text.Contains(_, name ) ),
each Combiner.CombineTextByDelimiter(" ; ", QuoteStyle.None)(List.RemoveNulls(_) ),
name
)
),
Result = if List.IsEmpty( ColumnsToMerge ) then Source else MergedColumns
in
Result
Thanks for your help again, Lorenzo
Unfortunately, when I open the files, the renaming does not appear to have been successful:
If I didn't do a good job of explaining myself in my post, please see this image that Riny_van_Eekelen posted above as the end result of his solution:
The column names above are exactly what I am after in this sample table 🙂
A better option (in Query5):
TransformedHeaders = Table.TransformColumnNames( MergedColumns, (name) => if Text.Contains( name, "(" ) then Text.BeforeDelimiter( Text.AfterDelimiter( name , "(" ), ")", {0, RelativePosition.FromEnd} ) else name ), Result = if List.IsEmpty( ColumnsToMerge ) then Source else TransformedHeaders in ResultThis (Query 5) looks awesome, Lorenzo 👏😊 I'll get to plugging it into my actual workbook and will return to mark it as the best response/leave feedback once I've got it up and running! Thanks again.
Lorenzo- I started responding with an update yesterday, and by the time that I hit "Reply", the platform had been taken offline for maintenance 😅
I plugged the code into my actual workbook and unfortunately found an issue with the result, I believe due to needing to merge headers that legitimately contain a bracket as part of their description; something that I had failed to include in my sample data. I created a deidentified/obfuscated table based off of my actual data, inserted Query5 into it, and attached a copy to this comment. I will follow this route in the future rather than trying to emulate my source data. It would be great if you could have a look at it to see if you are able to resolve the issue - I have marked the impacted headers up on the Output sheet.
Additionally, while no headers within the provided export/project contain numbers, it is possible that other data sets may - which would cause the merge step of this solution to fail if I understand correctly.
For some insight into the prevalence of numbers in headers of my exports, I opened a recent example and quickly found "Satisfaction score (out of 5)". This particular field will always have a singular value and therefore not be subject to merging but if you are able to account for numbers in the headers as part of your solution, it would be amazing as it would really enhance the solution's flexibility for my intended use. I am not certain if there will be a genuine need to merge columns with numbers in the header, so consider this as a huge value-add rather than being core to my current requirement. A new source table, query and output table have been inserted into the new sample data, also with markup for your review and consideration.
Any and all help is massively appreciated! Thanks again!🙇
EDIT: Unable to edit the original post to include the updated file. If any admins/mods see this comment, please can you add the file from this comment to my original post or replace the original file with this updated version. Thank you.
Apologies for missing the point re. the other headers. This can be addressed as follow (in attached Query4):
TransformedHeaders = Table.TransformColumnNames( MergedColumns, (name) => if Text.Contains( name, "(" ) then if Text.Contains( name, "))" ) then Text.BetweenDelimiters( name, "(", ")" , 0, 1 ) else Text.BetweenDelimiters( name, "(", ")" ) else name ), Result = if List.IsEmpty( ColumnsToMerge ) then Source else TransformedHeadersIn Query4 I also took another approach to identify the fields to merge. Won't work if you have fields where digits are in the middle of the field name:
ColumnsToMerge = List.Distinct( List.RemoveNulls( List.Transform( SourceLabelColumnNames, each let split = Splitter.SplitTextByCharacterTransition( (c) => not List.Contains({"0".."9"}, c), {"0".."9"})(_) in if List.Count( split ) > 1 then List.First( split ) else null ) ) )