My source data for many of my queries comes from .txt files extracted from a student database. Depending on the method used to export the data, sometimes my headers are capitalized and sometimes Title Case, or all lower. Anyway -- I find it quite tedious to update several queries that reference my main query in order to accommodate these format changes. Is there a way around this? How can I update all connected queries when simple formatting changes occur to row headers?
Thanks,
Cheryl
Hi Cheryl,
You can add a step after 'Source' with this formula which will change the text of headers to upper case:
= Table.TransformColumnNames(Source,Text.Upper)
If you prefer lower case you can use Text.Lower or for proper, Text.Proper in place of 'Text.Upper' in the formula above.
Mynda
Here is my base query - I have lots of other queries that either reference, or merge with this query in my file.
let
Source = Csv.Document(File.Contents("S:EFECheryl UlshDataEMCEMC PS Extract.txt"),[Delimiter=" ", Columns=26, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#Table.TransformColumnNames(Source,Text.Upper),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Replaced Value" = Table.ReplaceValue(#"Promoted Headers","","None",Replacer.ReplaceValue,{"EMCCollege_ID"})
in
#"Replaced Value"
What is the insertion point of this step? I have tried it in a number of places, but not gotten it to work.
Hi Cheryl,
You can do this in the GUI instead of the advanced editor, just select the 'Promoted Headers' step > click in the formula bar and type the formula (with an equal sign).
Alternatively, you need to make the changes below in red as you can't change the column headers until they're promoted to headers:
let
Source = Csv.Document(File.Contents("S:EFECheryl UlshDataEMCEMC PS Extract.txt"),[Delimiter=" ", Columns=26, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Correct Column Names" = Table.TransformColumnNames(#"Promoted Headers",Text.Upper),
#"Replaced Value" = Table.ReplaceValue(#"Correct Column Names","","None",Replacer.ReplaceValue,{"EMCCollege_ID"})
in
#"Replaced Value"
Note: your 'Replaced Value' step probably won't work because it's referring to a column name that may have changed due to the 'Correct Column Names' step. You'll probably need to delete that step and reapply.
Mynda
