Страницы: 1
RSS
PQ изменить формат всех столбцов кроме одного.
 
Добрый вечер. Подскажите, в таблице постоянно меняется количество столбцов и все они должны быть  в числовом формате, кроме одного - Сведено. Как это прописать в коде ? А точнее в самом последнем шаге. Сведено должен быть текстовым
Код
let
    Источник = Excel.Workbook(File.Contents("C:\Users\KovalYD\Desktop\СПКО\Е01 до справки.xlsx"), null, true),
    #"Е01 до справки_Sheet" = Источник{[Item="Е01 до справки",Kind="Sheet"]}[Data],
    #"Измененный тип" = Table.TransformColumnTypes(#"Е01 до справки_Sheet",{{"Column1", type text}, {"Column2", type text}, {"Column3", type any}, {"Column4", type any}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type any}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type text}, {"Column21", type text}, {"Column22", type text}, {"Column23", type text}, {"Column24", type text}, {"Column25", type any}, {"Column26", type text}, {"Column27", type any}, {"Column28", type any}, {"Column29", type text}, {"Column30", type any}, {"Column31", type any}, {"Column32", type text}, {"Column33", type text}, {"Column34", type text}, {"Column35", type text}, {"Column36", type any}, {"Column37", type any}, {"Column38", type text}, {"Column39", type text}, {"Column40", type text}, {"Column41", type text}, {"Column42", type text}, {"Column43", type any}, {"Column44", type text}, {"Column45", type any}, {"Column46", type any}, {"Column47", type any}, {"Column48", type any}, {"Column49", type any}, {"Column50", type any}, {"Column51", type any}, {"Column52", type any}, {"Column53", type any}, {"Column54", type any}, {"Column55", type any}, {"Column56", type text}, {"Column57", type any}, {"Column58", type any}, {"Column59", type any}, {"Column60", type any}, {"Column61", type any}, {"Column62", type any}, {"Column63", type any}, {"Column64", type any}, {"Column65", type any}, {"Column66", type text}, {"Column67", type any}, {"Column68", type text}, {"Column69", type any}, {"Column70", type text}, {"Column71", type text}, {"Column72", type text}, {"Column73", type any}, {"Column74", type any}, {"Column75", type any}, {"Column76", type any}, {"Column77", type any}, {"Column78", type any}, {"Column79", type any}, {"Column80", type text}, {"Column81", type any}, {"Column82", type any}, {"Column83", type any}, {"Column84", type any}, {"Column85", type any}, {"Column86", type any}, {"Column87", type text}, {"Column88", type any}, {"Column89", type any}, {"Column90", type text}, {"Column91", type any}, {"Column92", type text}, {"Column93", type any}, {"Column94", type any}, {"Column95", type any}, {"Column96", type text}, {"Column97", type any}, {"Column98", type text}, {"Column99", type text}, {"Column100", type any}, {"Column101", type text}, {"Column102", type text}, {"Column103", type text}, {"Column104", type text}, {"Column105", type text}, {"Column106", type text}, {"Column107", type any}, {"Column108", type text}, {"Column109", type text}, {"Column110", type text}, {"Column111", type text}, {"Column112", type text}, {"Column113", type any}, {"Column114", type text}, {"Column115", type any}, {"Column116", type text}, {"Column117", type text}, {"Column118", type any}, {"Column119", type text}, {"Column120", type text}, {"Column121", type text}, {"Column122", type text}, {"Column123", type any}, {"Column124", type any}, {"Column125", type text}, {"Column126", type any}, {"Column127", type any}, {"Column128", type any}, {"Column129", type text}, {"Column130", type text}, {"Column131", type any}, {"Column132", type any}, {"Column133", type any}, {"Column134", type text}, {"Column135", type any}, {"Column136", type any}, {"Column137", type text}, {"Column138", type text}, {"Column139", type text}, {"Column140", type text}, {"Column141", type text}, {"Column142", type text}, {"Column143", type any}, {"Column144", type any}, {"Column145", type text}, {"Column146", type any}, {"Column147", type any}, {"Column148", type text}, {"Column149", type text}, {"Column150", type text}, {"Column151", type any}, {"Column152", type text}, {"Column153", type text}, {"Column154", type text}, {"Column155", type text}, {"Column156", type text}, {"Column157", type text}, {"Column158", type text}, {"Column159", type text}, {"Column160", type text}, {"Column161", type text}, {"Column162", type text}, {"Column163", type text}, {"Column164", type text}, {"Column165", type text}, {"Column166", type text}, {"Column167", type text}, {"Column168", type text}, {"Column169", type text}, {"Column170", type text}, {"Column171", type any}, {"Column172", type any}, {"Column173", type any}, {"Column174", type any}, {"Column175", type any}, {"Column176", type text}, {"Column177", type text}, {"Column178", type text}, {"Column179", type text}, {"Column180", type text}, {"Column181", type text}, {"Column182", type text}, {"Column183", type text}, {"Column184", type any}, {"Column185", type any}, {"Column186", type text}, {"Column187", type text}, {"Column188", type any}, {"Column189", type text}, {"Column190", type any}, {"Column191", type any}, {"Column192", type any}, {"Column193", type text}, {"Column194", type text}, {"Column195", type text}, {"Column196", type text}, {"Column197", type text}, {"Column198", type text}, {"Column199", type text}, {"Column200", type text}, {"Column201", type any}, {"Column202", type any}, {"Column203", type text}, {"Column204", type any}, {"Column205", type any}, {"Column206", type text}, {"Column207", type any}, {"Column208", type text}, {"Column209", type any}, {"Column210", type any}, {"Column211", type any}, {"Column212", type text}, {"Column213", type any}, {"Column214", type any}, {"Column215", type any}, {"Column216", type any}, {"Column217", type any}, {"Column218", type any}, {"Column219", type text}, {"Column220", type text}, {"Column221", type text}, {"Column222", type any}, {"Column223", type any}, {"Column224", type any}, {"Column225", type text}, {"Column226", type text}, {"Column227", type text}, {"Column228", type any}, {"Column229", type any}, {"Column230", type any}, {"Column231", type any}, {"Column232", type any}, {"Column233", type any}, {"Column234", type text}, {"Column235", type text}, {"Column236", type text}, {"Column237", type text}, {"Column238", type any}, {"Column239", type any}, {"Column240", type any}, {"Column241", type any}, {"Column242", type any}, {"Column243", type text}, {"Column244", type text}, {"Column245", type text}, {"Column246", type text}, {"Column247", type text}, {"Column248", type text}, {"Column249", type text}, {"Column250", type text}, {"Column251", type text}, {"Column252", type text}, {"Column253", type text}}),
    #"Удаленные верхние строки" = Table.Skip(#"Измененный тип",1),
    #"Транспонированная таблица" = Table.Transpose(#"Удаленные верхние строки"),
    #"Разделить столбец по разделителю1" = Table.SplitColumn(#"Транспонированная таблица", "Column1", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Column1.1", "Column1.2"}),
    #"Транспонированная таблица1" = Table.Transpose(#"Разделить столбец по разделителю1"),
    #"Повышенные заголовки" = Table.PromoteHeaders(#"Транспонированная таблица1", [PromoteAllScalars=true]),
    #"Удаленные верхние строки1" = Table.Skip(#"Повышенные заголовки",1),
    #"Разделить столбец по разделителю" = Table.SplitColumn(#"Удаленные верхние строки1", "Контрагент", Splitter.SplitTextByEachDelimiter({"-"}, QuoteStyle.Csv, false), {"Контрагент.1", "Контрагент.2"}),
    #"Добавлен пользовательский объект" = Table.AddColumn(#"Разделить столбец по разделителю", "Пользовательская", each Text.Remove([Контрагент.2],{"0".."9","(",")"})),
    #"Замененное значение" = Table.ReplaceValue(#"Добавлен пользовательский объект","-"," ",Replacer.ReplaceText,{"Пользовательская"}),
    #"Удаленные столбцы" = Table.RemoveColumns(#"Замененное значение",{"Контрагент.1", "Контрагент.2", "CE", "CE_1"}),
    #"Переупорядоченные столбцы" = Table.ReorderColumns(#"Удаленные столбцы",{"Пользовательская", "Характер", "801010", "801020", "801555", "802010", "802021", "802022", "802029", "802555", "803100", "803210", "803220", "803230", "803240", "803250", "803300", "803555", "804000", "804555", "805000", "805555", "806011", "806111", "806121", "806012", "806112", "806122", "806020", "806030", "806041", "806311", "806312", "806321", "806322", "806401", "806402", "806500", "806555", "807010", "807020", "807030", "807040", "807051", "807059", "807061", "807062", "807063", "807069", "807070", "807080", "807090", "807100", "807110", "807121", "807122", "807123", "807124", "807129", "807130", "807140", "807150", "807161", "807162", "807169", "807170", "807990", "807555", "808050", "808060", "810101", "810102", "810311", "810321", "810331", "810341", "810350", "810555", "820101", "820102", "820311", "820321", "820331", "820350", "820555", "830100", "830200", "830300", "830400", "830555", "841100", "841200", "841300", "841555", "842100", "842200", "842300", "842500", "842510", "842520", "842700", "842800", "842990", "842555", "845010", "845021", "845022", "845023", "845024", "845025", "845026", "845027", "845028", "845029", "845031", "845032", "845033", "845034", "845035", "845036", "845039", "845040", "845051", "845052", "845059", "845060", "845061", "845070", "845071", "845081", "845082", "845091", "845101", "845102", "845103", "845104", "845105", "845106", "845107", "845109", "845111", "845112", "845121", "845122", "845123", "845129", "845131", "845132", "845133", "845134", "845139", "845141", "845149", "845151", "845152", "845153", "845154", "845155", "845156", "845157", "845158", "845159", "845161", "845162", "845163", "845164", "845165", "845166", "845169", "845170", "845181", "845182", "845189", "845190", "845201", "845202", "845203", "845204", "845301", "845555", "845991", "845992", "845993", "845994", "845995", "845996", "845997", "845998", "845999", "846030", "846040", "846050", "846060", "846090", "846555", "847121", "847200", "847555", "CE_2", "844000", "844555", "843100", "843200", "843555", "843900", "830555_3", "830555_4", "830555_5", "830555_6", "DIS3_NA", "DIS3_NA_7", "DIS3_NA_8", "DIS3_NA_9", "DIS3_NA_10", "DIS3_NA_11", "DIS3_NA_12", "DIS3_NA_13", "DIS3_NA_14", "DIS3_NA_15", "DIS3_NA_16", "DIS3_NA_17", "DIS3_NA_18", "DIS3_NA_19", "DIS3_NA_20", "DIS3_NA_21", "CE_22", "DIS3_NA_23", "DIS3_NA_24", "830555_25", "848000", "848110", "848120", "848131", "848132", "848133", "848139", "848150", "848160", "848170", "848180", "848201", "848209", "848301", "848302", "848309", "848400", "848501", "848502", "848509", "848555", "848600"}),
    #"Объединенные столбцы" = Table.CombineColumns(#"Переупорядоченные столбцы",{"Пользовательская", "Характер"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Сведено"),
    #"Измененный тип1" = Table.TransformColumnTypes(#"Объединенные столбцы",List.Transform(Table.ColumnNames(#"Объединенные столбцы"), each {_, type number}))
in
    #"Измененный тип1"
 
del
Изменено: buchlotnik - 23.08.2021 15:29:01
Соблюдение правил форума не освобождает от модераторского произвола
 
buchlotnik, спасибо. все работает ))) ура ура )))
Файл с рабочего компьютера не могу скинуть. даже такой вариант который вы показали меня устраивает ))
Страницы: 1
Наверх