Search code examples
excelpowerbipowerquerym

Change type of each column contingent to criteria

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"
Solution

  • 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}))