change type of each column contingent to criteria

[Anonymous]

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"

[Anonymous]

Hi @Anonymous ,

Power BI supports you to calculate by column. We couldn't get column header as data in our calculate. According to your statements, I think you want to get column header and compare them with data in another table, then change the column type. It is not supported in Power BI.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Assuming this is for Power Query inside Excel (as it would not work in Power BI) - what is your actual question?