I would like to change to ODBC drivers rather than standard database drivers for getting data because it avoids the problem with certificates.
I had hoped it would be as easy as cutting and pasting the M code from the Query but sadly it is not. I have an error that I do not know how to correct.
The original Query was:
let
Source = PostgreSQL.Database(“piweb-data.c7l2ckqf1eq5.eu-west-1.rds.amazonaws.com”, “client_data”),
processed_data_new_data_translated = Source{[Schema=“processed_data”,Item=“new_data_translated”]}[Data],
#“Split Column by Delimiter” = Table.SplitColumn(processed_data_new_data_translated, “customer_info”, Splitter.SplitTextByDelimiter(“~”, QuoteStyle.Csv), {“customer_info.1”, “customer_info.2”, “customer_info.3”, “customer_info.4”, “customer_info.5”, “customer_info.6”, “customer_info.7”, “customer_info.8”, “customer_info.9”, “customer_info.10”, “customer_info.11”, “customer_info.12”, “customer_info.13”, “customer_info.14”, “customer_info.15”}),
#“Changed Type” = Table.TransformColumnTypes(#“Split Column by Delimiter”,{{“customer_info.1”, type text}, {“customer_info.2”, Int64.Type}, {“customer_info.3”, Int64.Type}, {“customer_info.4”, type text}, {“customer_info.5”, type text}, {“customer_info.6”, type text}, {“customer_info.7”, type text}, {“customer_info.8”, Int64.Type}, {“customer_info.9”, type text}, {“customer_info.10”, type text}, {“customer_info.11”, type text}, {“customer_info.12”, type text}, {“customer_info.13”, type text}, {“customer_info.14”, Int64.Type}, {“customer_info.15”, type text}}),
#“Renamed Columns” = Table.RenameColumns(#“Changed Type”,{{“customer_info.1”, “PiWebCustomerID”}, {“customer_info.2”, “GamePiAssetNo”}, {“customer_info.3”, “HopperAlertLevel”}, {“customer_info.4”, “CustomerSiteID”}, {“customer_info.5”, “CustomerSiteType”}, {“customer_info.6”, “CustomerSitePostCode”}, {“customer_info.7”, “CustomerMachineCode”}, {“customer_info.8”, “CustomerMachineAssetNo”}, {“customer_info.9”, “CustomerMachineCategory”}, {“customer_info.10”, “BreweryAmedisD”}, {“customer_info.11”, “OperatorAmedisID”}, {“customer_info.12”, “BreweryHouseCode”}, {“customer_info.13”, “BreweryMachinePosition”}, {“customer_info.14”, “BactaMachineType”}, {“customer_info.15”, “AmedisModel”}}),
#“Duplicated Column” = Table.DuplicateColumn(#“Renamed Columns”, “event_time”, “event_time - Copy”),
#“Renamed Columns2” = Table.RenameColumns(#“Duplicated Column”,{{“event_time - Copy”, “EventDateTime”}}),
#“Split Column by Delimiter1” = Table.SplitColumn(Table.TransformColumnTypes(#“Renamed Columns2”, {{“event_time”, type text}}, “en-GB”), “event_time”, Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {“event_time.1”, “event_time.2”}),
#“Changed Type1” = Table.TransformColumnTypes(#“Split Column by Delimiter1”,{{“event_time.1”, type date}, {“event_time.2”, type time}}),
#“Renamed Columns1” = Table.RenameColumns(#“Changed Type1”,{{“event_time.1”, “EventDate”}, {“event_time.2”, “EventTime”}, {“CustomerMachineAssetNo”, “Asset No”}})
in
#“Renamed Columns1”
The new query which has a syntax problem reads
= let
Source = Odbc.DataSource(“dsn=PostgreSQL35W”, [HierarchicalNavigation=true]),
client_data_Database = Source{[Name=“client_data”,Kind=“Database”]}[Data],
processed_data_Schema = client_data_Database{[Name=“processed_data”,Kind=“Schema”]}[Data],
new_data_translated_Table = processed_data_Schema{[Name=“processed_data new_data_translated”,Kind=“Table”]}[Data]
in
new_data_translated_Table
#“Split Column by Delimiter” = Table.SplitColumn(processed_data_new_data_translated, “customer_info”, Splitter.SplitTextByDelimiter(“~”, QuoteStyle.Csv), {“customer_info.1”, “customer_info.2”, “customer_info.3”, “customer_info.4”, “customer_info.5”, “customer_info.6”, “customer_info.7”, “customer_info.8”, “customer_info.9”, “customer_info.10”, “customer_info.11”, “customer_info.12”, “customer_info.13”, “customer_info.14”, “customer_info.15”}),
#“Changed Type” = Table.TransformColumnTypes(#“Split Column by Delimiter”,{{“customer_info.1”, type text}, {“customer_info.2”, Int64.Type}, {“customer_info.3”, Int64.Type}, {“customer_info.4”, type text}, {“customer_info.5”, type text}, {“customer_info.6”, type text}, {“customer_info.7”, type text}, {“customer_info.8”, Int64.Type}, {“customer_info.9”, type text}, {“customer_info.10”, type text}, {“customer_info.11”, type text}, {“customer_info.12”, type text}, {“customer_info.13”, type text}, {“customer_info.14”, Int64.Type}, {“customer_info.15”, type text}}),
#“Renamed Columns” = Table.RenameColumns(#“Changed Type”,{{“customer_info.1”, “PiWebCustomerID”}, {“customer_info.2”, “GamePiAssetNo”}, {“customer_info.3”, “HopperAlertLevel”}, {“customer_info.4”, “CustomerSiteID”}, {“customer_info.5”, “CustomerSiteType”}, {“customer_info.6”, “CustomerSitePostCode”}, {“customer_info.7”, “CustomerMachineCode”}, {“customer_info.8”, “CustomerMachineAssetNo”}, {“customer_info.9”, “CustomerMachineCategory”}, {“customer_info.10”, “BreweryAmedisD”}, {“customer_info.11”, “OperatorAmedisID”}, {“customer_info.12”, “BreweryHouseCode”}, {“customer_info.13”, “BreweryMachinePosition”}, {“customer_info.14”, “BactaMachineType”}, {“customer_info.15”, “AmedisModel”}}),
#“Duplicated Column” = Table.DuplicateColumn(#“Renamed Columns”, “event_time”, “event_time - Copy”),
#“Renamed Columns2” = Table.RenameColumns(#“Duplicated Column”,{{“event_time - Copy”, “EventDateTime”}}),
#“Split Column by Delimiter1” = Table.SplitColumn(Table.TransformColumnTypes(#“Renamed Columns2”, {{“event_time”, type text}}, “en-GB”), “event_time”, Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {“event_time.1”, “event_time.2”}),
#“Changed Type1” = Table.TransformColumnTypes(#“Split Column by Delimiter1”,{{“event_time.1”, type date}, {“event_time.2”, type time}}),
#“Renamed Columns1” = Table.RenameColumns(#“Changed Type1”,{{“event_time.1”, “EventDate”}, {“event_time.2”, “EventTime”}, {“CustomerMachineAssetNo”, “Asset No”}})
in
#“Renamed Columns1”
Any help gratefully received.
Paul
