change data source to sql server Power bi crashes during creating connection in model

Anonymous 

mwegener 

Here is the M Code for Excel source

let
    Source = Excel.Workbook(File.Contents("C:\Users\HEL_TPatel\OneDrive - Magna\Desktop\SPS_bvo2_new.xlsx"), null, true),
    Tabelle1_Sheet = Source{[Item="Table9",Kind="Table"]}[Data],
    #"Changed Type with Locale" = Table.TransformColumnTypes(Tabelle1_Sheet, {{"DateTime", type datetime}}, "bn-IN"),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type with Locale"," _","__",Replacer.ReplaceText,{"txtName"}),
    #"Filtered Rows2" = Table.SelectRows(#"Replaced Value", each ([SPS_Bereich] = "helpebvo2")),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows2", "txtName", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"txtName.1", "txtName.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"txtName.1", type text}, {"txtName.2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"txtName.2", "Message"}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns",{"txtCame"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns1",{{"txtName.1", "Machine"}}),
    #"Grouped Rows" = Table.Group(#"Renamed Columns1", {"DateTime"}, {{"TimeDiff", each List.Max([TimeDiff]), type number}, {"Details", each _, type table [DWH_ID=number, SPS_Bereich=text, ID=number, DateTime=datetime, Classname=text, Machine=text, Message=text, TimeDiff=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Top", each Table.Max ([Details], "TimeDiff")),
    #"Removed Columns2" = Table.RemoveColumns(#"Added Custom",{"Details"}),
    #"Expanded Top" = Table.ExpandRecordColumn(#"Removed Columns2", "Top", {"ID", "Machine", "Message"}, {"ID", "Machine", "Message"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Top", each [TimeDiff] >= 10),
    #"Added Custom1" = Table.AddColumn(#"Filtered Rows", "Custom", each [DateTime]+#duration(0,0,0,[TimeDiff])),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom", type datetime}}),
    #"Changed Type5" = Table.TransformColumnTypes(#"Changed Type2",{{"ID", Int64.Type}}),
    #"Renamed Columns3" = Table.RenameColumns(#"Changed Type5",{{"Custom", "datewithaddedsecond"}}),
    #"Renamed Columns5" = Table.RenameColumns(#"Renamed Columns3",{{"ID", "Index"}}),
    #"Renamed Columns6" = Table.RenameColumns(#"Renamed Columns5",{{"DateTime", "Start Date"}, {"datewithaddedsecond", "End Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns6",{{"TimeDiff", Int64.Type}}),
    #"Inserted Date" = Table.AddColumn(#"Changed Type", "Date", each Date.From([Start Date]), type date),
    #"Filtered Rows1" = Table.SelectRows(#"Inserted Date", each ([Date] <> #date(2019, 1, 3))),
    #"Changed Type3" = Table.TransformColumnTypes(#"Filtered Rows1",{{"Machine", type text}, {"Message", type text}}),
    #"Renamed Columns2" = Table.RenameColumns(#"Changed Type3",{{"Index", "Index"}, {"Start Date", "Start Date"}})
in
    #"Renamed Columns2"

Here is the M Code for new SQL Server database 

let
    Source = Sql.Database("HELMSSQL04\INSTANZ1", "Stoerdaten", [Query="SELECT TOP 200 sto.[DWH_ID]#(lf)      ,sto.[SPS_Bereich]#(lf)     ,FORMAT(sto.[DateTime], 'dd-MM-yyyy HH:mm') as [Start Date]#(lf)          ,sto.[txtName]#(lf)       ,sto.[TimeDiff]#(lf)  FROM [Stoerdaten].[sta].[Stoerungen]  sto#(lf)  join #(lf)  [IgnitionServer].[dbo].[scheduled_events_ISTProduction] cal on #(lf)  DateTime #(lf)  between cal.start_date and cal.end_date #(lf)  where sto.Classname = 'Alarm' and sto.TimeDiff >60#(lf)     and cal.typ=1 order by id desc #(lf)"]),
    #"Filtered Rows" = Table.SelectRows(Source, each ([SPS_Bereich] = "helpebvo2")),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Filtered Rows", {{"Start Date", type datetime}}, "en-IN"),
    Index = Table.RenameColumns(#"Changed Type with Locale",{{"DWH_ID", "Index"}}),
    #"End Date column" = Table.AddColumn(Index, "End Date", each [Start Date]+#duration(0,0,0,[TimeDiff])),
    #"End Date change to Date Type" = Table.TransformColumnTypes(#"End Date column",{{"End Date", type datetime}}),
    #"Grouped Rows" = Table.Group(#"End Date change to Date Type", {"Start Date"}, {{"TimeDiff", each List.Max([TimeDiff]), type number}, {"Details", each _, type table [Index=number, SPS_Bereich=text, Start Date=datetime, txtName=text, TimeDiff=number, End Date=datetime]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Top", each Table.Max ([Details], "TimeDiff")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Details"}),
    #"Expanded Top" = Table.ExpandRecordColumn(#"Removed Columns", "Top", {"Index", "SPS_Bereich", "txtName", "End Date"}, {"Index", "SPS_Bereich", "txtName", "End Date"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Top",{{"Index", Int64.Type}, {"SPS_Bereich", type text}, {"txtName", type text}, {"End Date", type datetime}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type",{"Index", "SPS_Bereich", "Start Date", "TimeDiff", "End Date", "txtName"}),
    #"Replaced Value" = Table.ReplaceValue(#"Reordered Columns"," _","__",Replacer.ReplaceText,{"txtName"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value", "txtName", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"txtName.1", "txtName.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"txtName.1", type text}, {"txtName.2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"txtName.1", "Machine"}, {"txtName.2", "Message"}}),
    #"Inserted Date" = Table.AddColumn(#"Renamed Columns", "Date", each Date.From([Start Date]), type date),
    #"Filtered Rows1" = Table.SelectRows(#"Inserted Date", each ([End Date] <> #datetime(2020, 5, 28, 19, 47, 37)))
in
    #"Filtered Rows1"

There is only one column is new in the SQL server database which is SPS_Bereich other than that all column name are same at the end of the M-Code.

column order is diffrenet in New M-Code but i don't think that it cause problem.

Here is the screen shot which explain which table has excel and sql server datasource.

I would like to replace the M-code of ErrorLogs with SQL_ErrorLogs.

Here is the link for .pbix file

.pbix File 

Thanks for looking in to this problem.

Regads,

tar