Hi. Stil lcant solve this.
What i have done is move all the extra steps into the indiviudal table queries so for each company i have a table from this code
let
Source = Sql.Databases("PRD-SO-DW1.iia-cloud.com"),
BARC_empower_DW_REPORTING = Source{[Name="BARC_empower_DW_REPORTING"]}[Data],
Reporting_v_Dashboard_Contact_Count_Expanded = BARC_empower_DW_REPORTING{[Schema="Reporting",Item="v_Dashboard_Contact_Count_Expanded"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Reporting_v_Dashboard_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}}),
Buffer=Table.Buffer(#"Lowercased Text"),
#"Filtered Rows2" = Table.SelectRows(Buffer, each ([EMAIL] <> null) and ([Contact Type] <> "Inbound Call")),
#"Merged Queries1" = Table.NestedJoin(#"Filtered Rows2", {"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 Custom2" = Table.AddColumn(#"Expanded PhoneUser", "Check", each if Text.Contains([Email],"adviserplus.com") then "Y" else "N"),
#"Filtered Rows" = Table.SelectRows(#"Added Custom2", each ([Check] = "Y")),
#"Removed Other Columns2" = 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 Rows3" = Table.Sort(#"Removed Other Columns2",{{"Contact Date", Order.Descending}})
in
#"Sorted Rows3"
.this works fine and i can see the indiviual company tables are refreshiung in service
The last step is just a simple combine of the 10 companies
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"))
in
#"Filtered Rows1"
This bit still doesnt work in service. I am really struggling as I cannot see how this step cannot be handled as no query folding involved?
