Grouping information from multiple tables

[NZCraig]

Hi All,

I have an problem with querying data that comes from multiple SQL tables and outputs to a format such as Excel, so that I can  identify gaps in data for end users and provide them a tool where they can record any data that needs to be completed.

My example tables are as follows:

jobplan table

jpnum	description	duration	status	jobplanid
JP1000	JP Description 1	8	Active	2101

jobtask table

jptask	description	jpnum	jobplanid
10	JP task 1	JP1000	2101
20	JP task 2	JP1000	2101
10	JP task 1	JP1000	1999
20	JP task 2	JP1000	1999

joblabour table

craft	quantity	laborhrs	jptask	jobplanid	jpnum
ELEC	2	8		2101	JP1000
EXT-ELEC	1	4	20	2101	JP1000
MECH	2	8		2101	JP1000
TA	4	12	10	2101	JP1000
EXT-MECH	1	4	10	2101	JP1000

jobmaterial table

itemnum	description	quantity	jobplanid	jpnum	jptask
122	GASKET	1	2101	JP1000	20
543	Nut	2	2101	JP1000	20
67895	Bolt	1	2101	JP1000	20
123	GASKET	1	1999	JP1000	20

The desired output needs to be able to show a table that combines all this data, without duplicating all the rows for all the variables and group the common data to make it easier to read. Apologies if this isn't quite the righgt forum for this but I was hoping to do this in power query so that i can adjust the tool dynamically as data is updated. Happy to have a discussion in another forum, or offline from here if there is someone who has a solution, btu this isn't quite where it fits.

Thanks in advance, Craig

Desired output:

jpnum	description	duration	status	jobplanid	jptask	description	jpnum	jobplanid	craft	quantity	laborhrs	jptask	jobplanid	jpnum	itemnum	description	quantity	jobplanid	jpnum	jptask
JP1000	JP Description 1	8	Active	2101	10	JP task 1	JP1000	2101	TA	4	12	10	2101	JP1000
									EXT MECH	1	4	10	2101	JP1000
					20	JP task 2	JP1000	2101	EXT-ELEC	1	4	20	2101	JP1000	122	GASKET	1	2101	JP1000	20
															543	Nut	2	2101	JP1000	20
															67895	Bolt	1	2101	JP1000	20
									ELEC	2	8		2101	JP1000
									MECH	2	8		2101	JP1000

[wdx223_Daniel · Accepted solution]

= List.Accumulate({"JobPlan","JobTask","JobLabour","JobMaterial"},"",(x,y)=>let a=Record.Field(#shared,y),b=List.Intersect({{"jpnum","jobplanid","jptask"},Table.ColumnNames(a),Table.ColumnNames(x)}),c=Table.ColumnNames(a),d=List.Transform(c,each if List.Contains(Table.ColumnNames(x),_) then y&"-"&_ else _),e=Table.NestedJoin(x,b,a,b,"n",JoinKind.FullOuter) in if x="" then a else Table.ExpandTableColumn(e,"n",c,d))

[NZCraig]

Hi Daniel, thanks for having a crack, this isn't quitre what I was after , but i'll keep working through it and see what I can get.