Hello
I am importing a folder (just one file for now). I want all columns to be transformed into text.
Is there any smart way to do so or do I need to change the data type for every single column manually? Here is the code:
thanks a lot
Marek
let
Source = Folder.Files("C:\Users\mxr0520\Desktop\HR Control Tower\Baseline"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"PS ID", Int64.Type}, {"Empl Record", Int64.Type}, {"Name", type text}, {"Payroll System Number", Int64.Type}, {"Network ID", type text}, {"Business Unit HFM Final", type text}, {"Financial Contact", type text}, {"HR Contact", type text}, {"Hire Date", type date}, {"Maternity Leave", type any}, {"Unit", type text}, {"Business Unit", type text}, {"Co", type text}, {"CO Descr", type text}, {"Dept ID", type text}, {"Dept", type text}, {"Location", Int64.Type}, {"Location LDescr", type text}, {"Country", type text}, {"Job Code", type text}, {"Job Title", type text}, {"Business Title", type text}, {"Supv ID", Int64.Type}, {"Supv Name", type text}, {"Pay Status", type text}, {"Type", type text}, {"Reg/Temp", type text}, {"Full/Part", type text}, {"HFM type mapping", type text}, {"Cost Center", Int64.Type}, {"HFM Co", Int64.Type}, {"HFM Prime", Int64.Type}, {"Regular Local Cost Center", type text}, {"CC Expns Ovr", Int64.Type}, {"Internal Order", type text}, {"HFM Co Override", Int64.Type}, {"HFM Prime_Override", Int64.Type}, {"Override Local Cost Center", type text}, {"Final Cost Center", Int64.Type}, {"Final HFM Co", Int64.Type}, {"Final HFM Prime", Int64.Type}, {"Final HFM Co Adjusted", Int64.Type}, {"Cluster SSE", type text}, {"Final Local Cost Center", type text}, {"BA&R Entity", type any}, {"BA&R Function", type any}, {"BA&R Parent Entity", type any}, {"Org Relation", type text}, {"Comments", type any}, {"Line in prior report", Int64.Type}, {"New?", type text}, {"Empl Record_1", type text}, {"Name_2", type text}, {"Payroll System Number_3", type text}, {"Network ID_4", type text}, {"Business Unit HFM Final_5", type text}, {"Financial Contact_6", type text}, {"HR Contact_7", type text}, {"Hire Date_8", type text}, {"Maternity Leave_9", type text}, {"Unit_10", type text}, {"Business Unit_11", type text}, {"Co_12", type text}, {"CO Descr_13", type text}, {"Dept ID_14", type text}, {"Dept_15", type text}, {"Location_16", type text}, {"Location LDescr_17", type text}, {"Country_18", type text}, {"Job Code_19", type text}, {"Job Title_20", type text}, {"Business Title_21", type text}, {"Supv ID_22", type text}, {"Supv Name_23", type text}, {"Pay Status_24", type text}, {"Type_25", type text}, {"Reg/Temp_26", type text}, {"Full/Part_27", type text}, {"HFM type mapping_28", type text}, {"Cost Center_29", type text}, {"HFM Co_30", type text}, {"HFM Prime_31", type text}, {"Regular Local Cost Center_32", type text}, {"CC Expns Ovr_33", type text}, {"Internal Order_34", type text}, {"HFM Co Override_35", type text}, {"HFM Prime_Override_36", type text}, {"Override Local Cost Center_37", type text}, {"Final Cost Center_38", type text}, {"Final HFM Co_39", type text}, {"Final HFM Prime_40", type text}, {"Final Local Cost Center_41", type text}})
in
#"Changed Type"
