let
fnCamelCases = (Source)=> //by Sergey Lossev https://datachant.com/2018/02/14/split-camelcase-headers-m/
let
to_list = Text.ToList(Source),
to_table = Table.FromList(to_list, (x)=>{x, Text.Lower(x)=x}, {"word", "isLower"}),
group_chars = Table.Group(to_table, {"isLower"}, {{"word", each Text.Combine([word])}}, 0, (a,b)=>if b[isLower] then 0 else 1),
len = Table.AddColumn(group_chars, "len", each Text.Length([word])),
group_words = Table.Group(len, {"len"}, {{"word", each Text.Combine([word])}}, 0, (a,b)=>if (a[len]=1 and b[len]=1) then 0 else 1),
combine = Text.Combine(group_words[word], " ")
in
combine,
Source = Csv.Document(Web.Contents("https://www.planetaexcel.ru/bitrix/components/bitrix/forum.interface/show_file.php?fid=367107&action=download"),[Delimiter=","]),
PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
ChangedType = Table.TransformColumnTypes(PromotedHeaders,{{"subject", type text}, {"t1", type text}, {"fond", type text}, {"salary", type text}, {"monthyear", type date}, {"year", Int64.Type}}),
Camel = Table.TransformColumns(ChangedType, {{"subject", each fnCamelCases( _ ), type text}}),
ReplacedValue = Table.ReplaceValue(Camel,"( "," (",Replacer.ReplaceText,{"subject"}),
ReplacedValue1 = Table.ReplaceValue(ReplacedValue,"- ","-",Replacer.ReplaceText,{"subject"}),
ReplacedValue2 = Table.ReplaceValue(ReplacedValue1,"— ","—",Replacer.ReplaceText,{"subject"}),
ReplacedValue3 = Table.ReplaceValue(ReplacedValue2,"край"," край",Replacer.ReplaceText,{"subject"}),
ReplacedValue4 = Table.ReplaceValue(ReplacedValue3,"область"," область",Replacer.ReplaceText,{"subject"}),
ReplacedValue5 = Table.ReplaceValue(ReplacedValue4,"автоном"," автоном",Replacer.ReplaceText,{"subject"}),
ReplacedValue6 = Table.ReplaceValue(ReplacedValue5,"округ"," округ",Replacer.ReplaceText,{"subject"})
in
ReplacedValue6 |