Problem appending multiple queries

[tylerkbt]

I'm attempting to append 8 queries in a dataflow and I'm getting a pretty generic error: We're sorry, an error occured during evaluation.

When I go to show details it has the same message with request and session ID as well as the mashup script that I'll post below in a spoiler tag. First time posting on here, so if I didn't follow proper etiquette with posting my mashup script please let me know.

Thanks!

Spoiler

---------- Message ----------
We're sorry, an error occurred during evaluation.

---------- Session ID ----------
af1c9e50-f23b-429d-abae-8b6c3f6e5f4a

---------- Request ID ----------
be307886-e5c9-423a-8463-98773916098e

---------- Mashup script ----------
section Section1;
shared crb50_cases = let
Source = CommonDataService.Database("capsclientapp.crm.dynamics.com", [CreateNavigationProperties = null]),
#"Navigation 1" = Source{[Schema = "dbo", Item = "crb50_cases"]}[Data],
#"Filtered rows" = Table.SelectRows(#"Navigation 1", each [crb50_opendate] >= #datetime(2020, 1, 1, 0, 0, 0) and [crb50_closedate] >= #datetime(2020, 1, 1, 0, 0, 0) or [crb50_closedate] = null)
in
#"Filtered rows";
shared crb50_families = let
Source = CommonDataService.Database("capsclientapp.crm.dynamics.com", [CreateNavigationProperties = null]),
Navigation = Source{[Schema = "dbo", Item = "crb50_families"]}[Data]
in
Navigation;
shared crb50_adultmembers = let
Source = CommonDataService.Database("capsclientapp.crm.dynamics.com", [CreateNavigationProperties = null]),
#"Navigation 1" = Source{[Schema = "dbo", Item = "crb50_adultmembers"]}[Data],
#"Removed columns" = Table.RemoveColumns(#"Navigation 1", {"createdon", "createdby", "modifiedon", "modifiedby", "createdonbehalfby", "modifiedonbehalfby", "createdbyname", "createdbyyominame", "createdonbehalfbyname", "createdonbehalfbyyominame", "modifiedbyname", "modifiedbyyominame", "modifiedonbehalfbyname", "modifiedonbehalfbyyominame", "owningteam", "statecode", "statecodename", "statuscode", "statuscodename", "importsequencenumber", "overriddencreatedon", "timezoneruleversionnumber", "utcconversiontimezonecode", "crb50_race", "crb50_spanish", "crb50_gender", "crb50_maritalstatus", "crb50_county", "crb50_city", "crb50_cellphone", "crb50_maritalstatusname", "crb50_homephone", "crb50_county1", "crb50_county1name", "owningbusinessunitname", "crb50_adultmemberslegacyid", "owningbusinessunit"}),
#"Merged queries" = Table.NestedJoin(#"Removed columns", {"crb50_family"}, crb50_families, {"crb50_familiesid"}, "crb50_families", JoinKind.LeftOuter)
in
#"Merged queries";
shared crb50_familyappointments = let
Source = CommonDataService.Database("capsclientapp.crm.dynamics.com", [CreateNavigationProperties = null]),
#"Navigation 1" = Source{[Schema = "dbo", Item = "crb50_familyappointments"]}[Data],
#"Removed columns" = Table.RemoveColumns(#"Navigation 1", {"crb50_pfs", "createdon", "createdby", "modifiedon", "modifiedby", "createdonbehalfby", "modifiedonbehalfby", "createdbyname", "createdbyyominame", "createdonbehalfbyname", "createdonbehalfbyyominame", "modifiedbyname", "modifiedbyyominame", "modifiedonbehalfbyname", "modifiedonbehalfbyyominame", "owneridyominame", "owningbusinessunit", "owninguser", "owningteam", "statecode", "statecodename", "statuscode", "statuscodename", "importsequencenumber", "overriddencreatedon", "timezoneruleversionnumber", "utcconversiontimezonecode", "crb50_cancelled", "crb50_pfsurvey", "crb50_iy1on1", "crb50_courtassist", "crb50_sanesart", "crb50_facetoface", "crb50_dvack", "crb50_directindirect", "crb50_victimcomp", "crb50_aapi", "crb50_email", "crb50_office", "crb50_miles", "crb50_advhours", "crb50_home", "crb50_kips", "crb50_crisis", "crb50_np1on1", "crb50_cac", "crb50_closedate", "crb50_dcf", "crb50_letter", "crb50_pfa", "crb50_paperhelp", "crb50_mentorhours", "crb50_phone", "crb50_followup", "crb50_pfapfs", "crb50_miles2", "crb50_miles3", "crb50_nscs", "crb50_strengtheningfamilies", "crb50_toaddress2", "crb50_toaddress3", "crb50_asq", "crb50_noshow", "crb50_jja", "crb50_cans", "crb50_ecbg", "crb50_satisfactionsurvey", "crb50_fromaddress2", "crb50_fromaddress3", "crb50_travelcode2", "crb50_travelcode2name", "crb50_travelcode3", "crb50_travelcode3name", "crb50_kdoc", "crb50_servicetypes", "crb50_afterhours", "crb50_testdate"}),
#"Merged queries" = Table.NestedJoin(#"Removed columns", {"crb50_family"}, crb50_families, {"crb50_familiesid"}, "crb50_families (2)", JoinKind.LeftOuter),
#"Merged queries 1" = Table.NestedJoin(#"Merged queries", {"crb50_case"}, crb50_cases, {"crb50_casesid"}, "crb50_cases (2)", JoinKind.LeftOuter),
#"Expanded crb50_cases (2)" = Table.ExpandTableColumn(#"Merged queries 1", "crb50_cases (2)", {"crb50_casesid", "crb50_child8", "crb50_child7", "crb50_child6", "crb50_child5", "crb50_child4", "crb50_adult3", "crb50_child3", "crb50_adult2", "crb50_child2", "crb50_child1", "crb50_adult1"}, {"crb50_casesid", "crb50_child8", "crb50_child7", "crb50_child6", "crb50_child5", "crb50_child4", "crb50_adult3", "crb50_child3", "crb50_adult2", "crb50_child2", "crb50_child1", "crb50_adult1"})
in
#"Expanded crb50_cases (2)";
shared crb50_childmembers = let
Source = CommonDataService.Database("capsclientapp.crm.dynamics.com", [CreateNavigationProperties = null]),
#"Navigation 1" = Source{[Schema = "dbo", Item = "crb50_childmembers"]}[Data],
#"Merged queries" = Table.NestedJoin(#"Navigation 1", {"crb50_childmembersid"}, crb50_familyappointments, {"crb50_child1"}, "crb50_familyappointments", JoinKind.LeftOuter),
#"Merged queries 1" = Table.NestedJoin(#"Merged queries", {"crb50_family"}, crb50_families, {"crb50_familiesid"}, "crb50_families", JoinKind.LeftOuter),
#"Expanded crb50_families" = Table.ExpandTableColumn(#"Merged queries 1", "crb50_families", {"crb50_familyname"}, {"crb50_familyname.1"}),
#"Renamed columns" = Table.RenameColumns(#"Expanded crb50_families", {{"crb50_familyname.1", "crb50_familyname.2"}})
in
#"Renamed columns";
shared #"crb50_childmembers (old)" = let
Source = crb50_childmembers,
#"Merged queries" = Table.NestedJoin(Source, {"crb50_childmembersid"}, crb50_familyappointments, {"crb50_child1"}, "crb50_familyappointments (2)", JoinKind.LeftOuter),
#"Expanded crb50_familyappointments" = Table.ExpandTableColumn(#"Merged queries", "crb50_familyappointments", {"crb50_apptid", "crb50_casename", "crb50_familyname"}, {"crb50_apptid", "crb50_casename", "crb50_familyname.1"}),
#"Expanded crb50_cases(crb50_childmembersid)" = Table.ExpandTableColumn(#"Expanded crb50_familyappointments", "crb50_cases(crb50_childmembersid)", {"crb50_opendate", "crb50_closedate", "crb50_closed", "crb50_child8", "crb50_child7", "crb50_child6", "crb50_child5", "crb50_child4", "crb50_child3", "crb50_child2", "crb50_child1"}, {"crb50_opendate", "crb50_closedate", "crb50_closed", "crb50_child8", "crb50_child7", "crb50_child6", "crb50_child5", "crb50_child4", "crb50_child3", "crb50_child2", "crb50_child1"}),
#"Filtered rows" = Table.SelectRows(#"Expanded crb50_cases(crb50_childmembersid)", each [crb50_child1] <> null)
in
#"Filtered rows";
shared #"crb50_childmembers (1)" = let
Source = CommonDataService.Database("capsclientapp.crm.dynamics.com", [CreateNavigationProperties = null]),
#"Navigation 1" = Source{[Schema = "dbo", Item = "crb50_childmembers"]}[Data],
#"Merged queries" = Table.NestedJoin(#"Navigation 1", {"crb50_childmembersid"}, crb50_familyappointments, {"crb50_child1"}, "crb50_familyappointments", JoinKind.Inner),
#"Merged queries 1" = Table.NestedJoin(#"Merged queries", {"crb50_family"}, crb50_families, {"crb50_familiesid"}, "crb50_families", JoinKind.LeftOuter),
#"Expanded crb50_familyappointments" = Table.ExpandTableColumn(#"Merged queries 1", "crb50_familyappointments", {"crb50_apptid", "crb50_casename"}, {"crb50_apptid", "crb50_casename"}),
#"Expanded crb50_families" = Table.ExpandTableColumn(#"Expanded crb50_familyappointments", "crb50_families", {"crb50_familyname"}, {"crb50_familyname.1"}),
#"Renamed columns" = Table.RenameColumns(#"Expanded crb50_families", {{"crb50_familyname.1", "crb50_familyname.2"}}),
#"Filtered rows" = Table.SelectRows(#"Renamed columns", each ([crb50_apptid] <> null))
in
#"Filtered rows";
shared #"crb50_childmembers (2)" = let
Source = CommonDataService.Database("capsclientapp.crm.dynamics.com", [CreateNavigationProperties = null]),
#"Navigation 1" = Source{[Schema = "dbo", Item = "crb50_childmembers"]}[Data],
#"Merged queries" = Table.NestedJoin(#"Navigation 1", {"crb50_childmembersid"}, crb50_familyappointments, {"crb50_child2"}, "crb50_familyappointments", JoinKind.Inner),
#"Merged queries 1" = Table.NestedJoin(#"Merged queries", {"crb50_family"}, crb50_families, {"crb50_familiesid"}, "crb50_families", JoinKind.LeftOuter),
#"Expanded crb50_families" = Table.ExpandTableColumn(#"Merged queries 1", "crb50_families", {"crb50_familyname"}, {"crb50_familyname.1"}),
#"Renamed columns" = Table.RenameColumns(#"Expanded crb50_families", {{"crb50_familyname.1", "crb50_familyname.2"}}),
#"Expanded crb50_familyappointments" = Table.ExpandTableColumn(#"Renamed columns", "crb50_familyappointments", {"crb50_apptid", "crb50_casename"}, {"crb50_apptid", "crb50_casename"})
in
#"Expanded crb50_familyappointments";
shared #"crb50_childmembers (3)" = let
Source = CommonDataService.Database("capsclientapp.crm.dynamics.com", [CreateNavigationProperties = null]),
#"Navigation 1" = Source{[Schema = "dbo", Item = "crb50_childmembers"]}[Data],
#"Merged queries" = Table.NestedJoin(#"Navigation 1", {"crb50_childmembersid"}, crb50_familyappointments, {"crb50_child3"}, "crb50_familyappointments", JoinKind.Inner),
#"Merged queries 1" = Table.NestedJoin(#"Merged queries", {"crb50_family"}, crb50_families, {"crb50_familiesid"}, "crb50_families", JoinKind.LeftOuter),
#"Expanded crb50_families" = Table.ExpandTableColumn(#"Merged queries 1", "crb50_families", {"crb50_familyname"}, {"crb50_familyname.1"}),
#"Renamed columns" = Table.RenameColumns(#"Expanded crb50_families", {{"crb50_familyname.1", "crb50_familyname.2"}}),
#"Expanded crb50_familyappointments" = Table.ExpandTableColumn(#"Renamed columns", "crb50_familyappointments", {"crb50_apptid", "crb50_casename"}, {"crb50_apptid", "crb50_casename"})
in
#"Expanded crb50_familyappointments";
shared #"crb50_childmembers (4)" = let
Source = CommonDataService.Database("capsclientapp.crm.dynamics.com", [CreateNavigationProperties = null]),
#"Navigation 1" = Source{[Schema = "dbo", Item = "crb50_childmembers"]}[Data],
#"Merged queries" = Table.NestedJoin(#"Navigation 1", {"crb50_childmembersid"}, crb50_familyappointments, {"crb50_child4"}, "crb50_familyappointments", JoinKind.Inner),
#"Merged queries 1" = Table.NestedJoin(#"Merged queries", {"crb50_family"}, crb50_families, {"crb50_familiesid"}, "crb50_families", JoinKind.LeftOuter),
#"Expanded crb50_families" = Table.ExpandTableColumn(#"Merged queries 1", "crb50_families", {"crb50_familyname"}, {"crb50_familyname.1"}),
#"Renamed columns" = Table.RenameColumns(#"Expanded crb50_families", {{"crb50_familyname.1", "crb50_familyname.2"}}),
#"Expanded crb50_familyappointments" = Table.ExpandTableColumn(#"Renamed columns", "crb50_familyappointments", {"crb50_apptid", "crb50_casename"}, {"crb50_apptid", "crb50_casename"})
in
#"Expanded crb50_familyappointments";
shared #"crb50_childmembers (5)" = let
Source = CommonDataService.Database("capsclientapp.crm.dynamics.com", [CreateNavigationProperties = null]),
#"Navigation 1" = Source{[Schema = "dbo", Item = "crb50_childmembers"]}[Data],
#"Merged queries" = Table.NestedJoin(#"Navigation 1", {"crb50_childmembersid"}, crb50_familyappointments, {"crb50_child5"}, "crb50_familyappointments", JoinKind.Inner),
#"Merged queries 1" = Table.NestedJoin(#"Merged queries", {"crb50_family"}, crb50_families, {"crb50_familiesid"}, "crb50_families", JoinKind.LeftOuter),
#"Expanded crb50_families" = Table.ExpandTableColumn(#"Merged queries 1", "crb50_families", {"crb50_familyname"}, {"crb50_familyname.1"}),
#"Renamed columns" = Table.RenameColumns(#"Expanded crb50_families", {{"crb50_familyname.1", "crb50_familyname.2"}}),
#"Expanded crb50_familyappointments" = Table.ExpandTableColumn(#"Renamed columns", "crb50_familyappointments", {"crb50_apptid", "crb50_casename"}, {"crb50_apptid", "crb50_casename"})
in
#"Expanded crb50_familyappointments";
shared #"crb50_childmembers (6)" = let
Source = CommonDataService.Database("capsclientapp.crm.dynamics.com", [CreateNavigationProperties = null]),
#"Navigation 1" = Source{[Schema = "dbo", Item = "crb50_childmembers"]}[Data],
#"Merged queries" = Table.NestedJoin(#"Navigation 1", {"crb50_childmembersid"}, crb50_familyappointments, {"crb50_child6"}, "crb50_familyappointments", JoinKind.Inner),
#"Merged queries 1" = Table.NestedJoin(#"Merged queries", {"crb50_family"}, crb50_families, {"crb50_familiesid"}, "crb50_families", JoinKind.LeftOuter),
#"Expanded crb50_families" = Table.ExpandTableColumn(#"Merged queries 1", "crb50_families", {"crb50_familyname"}, {"crb50_familyname.1"}),
#"Renamed columns" = Table.RenameColumns(#"Expanded crb50_families", {{"crb50_familyname.1", "crb50_familyname.2"}}),
#"Expanded crb50_familyappointments" = Table.ExpandTableColumn(#"Renamed columns", "crb50_familyappointments", {"crb50_apptid", "crb50_casename"}, {"crb50_apptid", "crb50_casename"})
in
#"Expanded crb50_familyappointments";
shared #"crb50_childmembers (7)" = let
Source = CommonDataService.Database("capsclientapp.crm.dynamics.com", [CreateNavigationProperties = null]),
#"Navigation 1" = Source{[Schema = "dbo", Item = "crb50_childmembers"]}[Data],
#"Merged queries" = Table.NestedJoin(#"Navigation 1", {"crb50_childmembersid"}, crb50_familyappointments, {"crb50_child7"}, "crb50_familyappointments", JoinKind.Inner),
#"Merged queries 1" = Table.NestedJoin(#"Merged queries", {"crb50_family"}, crb50_families, {"crb50_familiesid"}, "crb50_families", JoinKind.LeftOuter),
#"Expanded crb50_families" = Table.ExpandTableColumn(#"Merged queries 1", "crb50_families", {"crb50_familyname"}, {"crb50_familyname.1"}),
#"Renamed columns" = Table.RenameColumns(#"Expanded crb50_families", {{"crb50_familyname.1", "crb50_familyname.2"}}),
#"Expanded crb50_familyappointments" = Table.ExpandTableColumn(#"Renamed columns", "crb50_familyappointments", {"crb50_apptid", "crb50_casename"}, {"crb50_apptid", "crb50_casename"}),
#"Appended query" = Table.Combine({#"Expanded crb50_familyappointments", #"crb50_childmembers (8)"})
in
#"Appended query";
shared #"crb50_childmembers (8)" = let
Source = CommonDataService.Database("capsclientapp.crm.dynamics.com", [CreateNavigationProperties = null]),
#"Navigation 1" = Source{[Schema = "dbo", Item = "crb50_childmembers"]}[Data],
#"Merged queries" = Table.NestedJoin(#"Navigation 1", {"crb50_childmembersid"}, crb50_familyappointments, {"crb50_child8"}, "crb50_familyappointments", JoinKind.Inner),
#"Merged queries 1" = Table.NestedJoin(#"Merged queries", {"crb50_family"}, crb50_families, {"crb50_familiesid"}, "crb50_families", JoinKind.LeftOuter),
#"Expanded crb50_families" = Table.ExpandTableColumn(#"Merged queries 1", "crb50_families", {"crb50_familyname"}, {"crb50_familyname.1"}),
#"Renamed columns" = Table.RenameColumns(#"Expanded crb50_families", {{"crb50_familyname.1", "crb50_familyname.2"}}),
#"Expanded crb50_familyappointments" = Table.ExpandTableColumn(#"Renamed columns", "crb50_familyappointments", {"crb50_apptid", "crb50_casename"}, {"crb50_apptid", "crb50_casename"})
in
#"Expanded crb50_familyappointments";

[ImkeF]

Hi @tylerkbt ,
unfortunately the error messages in dataflow are not very helpful yet.
What you can do instead is to copy all queries over to Power BI Desktop and do your query design there. Once finished, copy back to dataflow.
Copy paste is just one click: Check all the queries ->  Ctrl+C and in the other program, go to the queries pane and click Ctrl+V.