No still compeltely stuck.
So i changed the import to a direct query of Server like this
let
Source = Sql.Databases("PRD-SO-DW2.iia-cloud.com"),
BT_DW_REPORTING = Source{[Name="BT_DW_REPORTING"]}[Data],
dbo_v_Contact_Count_Expanded = BT_DW_REPORTING{[Schema="dbo",Item="v_Contact_Count_Expanded"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(dbo_v_Contact_Count_Expanded,{{"Contact Date", type datetime}}),
#"Filtered Rows1" = Table.SelectRows(#"Changed Type", each [Contact Date] > Loaddate),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows1",{"Contact Type", "Contact ID", "Contact Date", "Contact Time", "Case Number", "Topic Title", "Workflow Stage", "Workflow Step", "Adviser", "Client Name", "EMAIL"}),
#"Sorted Rows2" = Table.Sort(#"Removed Other Columns",{{"Case Number", Order.Ascending}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Sorted Rows2",{{"Contact Date", type date}, {"Contact Time", type time}}),
#"Added Custom" = Table.AddColumn(#"Changed Type2", "ContactTimeKey", each Text.Replace(Text.From([Contact Time], "en-GB"), ":", "")),
#"Sorted Rows" = Table.Sort(#"Added Custom",{{"Contact Date", Order.Descending}}),
#"Lowercased Text" = Table.TransformColumns(#"Sorted Rows",{{"EMAIL", Text.Lower, type text}})
in
#"Lowercased Text" instead of using the query. This made no difference overall
However this single table seems to load into the model with the latest data.
so the issue seems to be combining the tables with this query. Mostly this is just combining the tables , editing the emails as some have changed and then looking up their userid by email. Is this causing a query folding issue?
let
Source = Table.Combine({#"SLAs Sainsburys", #"SLAs BT", #"SLAs Currys", #"SLAs JLR", #"SLAs Barclays", #"SLAs Stark", #"SLAs Starbucks", #"SLAs NR", #"SLAs Maximus"}),
#"Filtered Rows1" = Table.SelectRows(Source, each ([EMAIL] <> null) and ([Contact Type] <> "Inbound Call")),
#"Merged Queries1" = Table.NestedJoin(#"Filtered Rows1", {"EMAIL"}, Email_Change, {"Old"}, "Email_Change", JoinKind.LeftOuter),
#"Expanded Email_Change" = Table.ExpandTableColumn(#"Merged Queries1", "Email_Change", {"New"}, {"New Email"}),
#"Added Custom1" = Table.AddColumn(#"Expanded Email_Change", "Email", each if [New Email]<>null then [New Email] else [EMAIL]),
#"Removed Other Columns1" = Table.SelectColumns(#"Added Custom1",{"Contact Type", "Contact ID", "Contact Date", "Contact Time", "Case Number", "Topic Title", "Workflow Stage", "Workflow Step", "Adviser", "Client Name", "ContactTimeKey", "Email"}),
#"Merged Queries" = Table.NestedJoin(#"Removed Other Columns1", {"Email"}, PhoneUser, {"Username"}, "PhoneUser", JoinKind.LeftOuter),
#"Expanded PhoneUser" = Table.ExpandTableColumn(#"Merged Queries", "PhoneUser", {"Name", "AdviserId"}, {"Name", "AdviserId"}),
#"Added Custom" = Table.AddColumn(#"Expanded PhoneUser", "Check", each if Text.Contains([Email],"adviserplus.com") then "Y" else "N"),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Check] = "Y")),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Contact Type", "Contact ID", "Contact Date", "Contact Time", "Case Number", "Topic Title", "Workflow Stage", "Workflow Step", "Client Name", "ContactTimeKey", "Name", "AdviserId", "Email"}),
#"Sorted Rows" = Table.Sort(#"Removed Other Columns",{{"Contact Date", Order.Descending}})
in
#"Sorted Rows"
