Forum Discussion
Grouping information from multiple tables
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 ...
= 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))
= 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))
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.