Change type of each column contingent to criteria-CodePudding

I would like to change the type of columns to type number under one out of two conditions (whichever works better): (1) each column except the first column or (2) each column whose column header is unequal to the term Balance Sheet.

Here's my code addressing both option (1) and option (2):

let
source_path=Excel.CurrentWorkbook(){[Name="rng_path"]}[Content]{0}[Column1],
ticker_name=Excel.CurrentWorkbook(){[Name="rng_ticker"]}[Content]{0}[Column1],
Quelle = Excel.Workbook(File.Contents(source_path & "\" & ticker_name & "_balance_sheet.xlsx"), null, true),
    
#"Sheet 1_Sheet" = Quelle{[Item="Sheet 1",Kind="Sheet"]}[Data],
#"Entfernte oberste Zeilen" = Table.Skip(#"Sheet 1_Sheet",1),
#"Höher gestufte Header" = Table.PromoteHeaders(#"Entfernte oberste Zeilen", [PromoteAllScalars=true]),

//option (1)
#"Geänderter Typ" = Table.TransformColumnTypes(#"Höher gestufte Header", List.Transform(Table.ColumnNames(#"Höher gestufte Header"), each {_, type number})),

//option (2)
#"Geänderter Typ" = Table.TransformColumnTypes(#"Höher gestufte Header", List.Transform(Table.ColumnNames(#"Höher gestufte Header"), each {_, each if Table.ColumnNames(#"Höher gestufte Header") <> "Balance Sheet" then type number else type text})),

#"Entfernte leere Zeilen" = Table.SelectRows(#"Geänderter Typ", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))
in
#"Entfernte leere Zeilen"

CodePudding user response：

To construct the column name transformation argument, I suggest:

 List.Zip({Table.ColumnNames(Source), {type text} & List.Repeat({type number}, Table.ColumnCount(Source)-1)})

For example, in the code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlLSUTIEYiM9cyBpoRSrE62UDOIDsTEQW4JFUqA8E5BqA7BQKpRrCjYAJALlmIF1x8YCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [a = _t, x = _t, y = _t, Column2 = _t]),
 
    typeList = List.Zip({
                    Table.ColumnNames(Source), 
                    {type text} & List.Repeat({type number}, Table.ColumnCount(Source)-1)
                    }),
                    
    #"Changed Type" = Table.TransformColumnTypes(Source,typeList)
in
    #"Changed Type"

Of course, you could combine steps 2 and 3 -- I separated them for clarity.

CodePudding user response：

The two methods could be

#"AllButFirst" = Table.TransformColumnTypes(Source,List.Transform(List.RemoveFirstN(Table.ColumnNames(Source),1),each {_, type number})),

#"BalanceSheetTitlesOnly" = Table.TransformColumnTypes(Source,List.Transform(List.Select(Table.ColumnNames(Source), each Text.Contains(_, "Balance Sheet")),each {_, type number}))