Grouping information from multiple tables

Question

Hi All,&nbsp;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&nbsp; identify gaps in data for end users and provide them a tool where they can record any data that needs to be completed.&nbsp;My example tables are as follows:jobplan tablejpnumdescriptiondurationstatusjobplanidJP1000JP Description 18Active2101&nbsp;jobtask tablejptaskdescriptionjpnumjobplanid10JP task 1JP1000210120JP task 2JP1000210110JP task 1JP1000199920JP task 2JP10001999&nbsp;joblabour tablecraftquantitylaborhrsjptaskjobplanidjpnumELEC28&nbsp;2101JP1000EXT-ELEC14202101JP1000MECH28&nbsp;2101JP1000TA412102101JP1000EXT-MECH14102101JP1000&nbsp;jobmaterial tableitemnumdescriptionquantityjobplanidjpnumjptask122GASKET12101JP100020543Nut22101JP10002067895Bolt12101JP100020123GASKET11999JP100020&nbsp;&nbsp;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.&nbsp;Thanks in advance, Craig&nbsp;Desired output:jpnumdescriptiondurationstatusjobplanidjptaskdescriptionjpnumjobplanidcraftquantitylaborhrsjptaskjobplanidjpnumitemnumdescriptionquantityjobplanidjpnumjptaskJP1000JP Description 18Active210110JP task 1JP10002101TA412102101JP1000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;EXT MECH14102101JP1000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;20JP task 2JP10002101EXT-ELEC14202101JP1000122GASKET12101JP100020&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;543Nut22101JP100020&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;67895Bolt12101JP100020&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ELEC28&nbsp;2101JP1000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MECH28&nbsp;2101JP1000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

wdx223_daniel · Accepted Answer

= List.Accumulate({"JobPlan","JobTask","JobLabour","JobMaterial"},"",(x,y)=&gt;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&amp;"-"&amp;_ else _),e=Table.NestedJoin(x,b,a,b,"n",JoinKind.FullOuter) in if x="" then a else Table.ExpandTableColumn(e,"n",c,d))
&nbsp;

Forum Discussion

Grouping information from multiple tables

2 Replies

Related content

Fabric & Azure Data User Group Program Requirements & Benefits

Community User Group Experience

Rest API Informations

Unpivot multiple groups

Missing information

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

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

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

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