Ability to query tables

[nazgul]

Is your feature request related to a problem? Please describe. I'd like to be able to use dataview to query data from spreadsheets and tables.

Describe the solution you'd like I recently had data from a spreadsheet that I wanted to consolidate by joining on certain column values and then generate a document with the results. Ideally I'd paste it into Obsidian and then manipulate it with dataview. My thought is that you can tag a table with ^id. You might use a prefix (e.g. ^dataview-id) so that dataview doesn't have to cache every single table. Internally dataview would cache the table as an array of records. And you'd find it in the file object under the ID.

Describe alternatives you've considered What I ended up doing was generating a CSV from Excel. Then converting the CSV to YAML. Putting the YAML in a document as frontmatter, and then processing it with dataview (see below). Then (because in this particular case, I wanted a static document), I saved the resulting datastructure as JSON and loaded the JSON into a templater file, which created a static document. But often just straight dataview would be fine.

Additional context Here's an example of the YAML I generated.

H1:
    -
        Team: xxx
        Section: Accomplished
        Theme: Code Health
        Task: Removing Dead Code
        Impact: 12KLOC removed xxx
        Count: 
        Description: "Deleted unused internal tool xxx"
        People: xyz
    -
        Team: yyy
        Section: Accomplished
        Theme: Code Health
        Task: zzz Reduction
        Impact: 
        Count: 7
        Description: "Remove zzz usage where it can violate privacy issues. Make remaining instances privacy-safe."
        People: abc

And the dataviewjs code (but it could have been straight dataview if it had been fully supported), since basically I'm just grouping by values.

var tab = dv.current().file.frontmatter.H1.groupBy(r => r.Section);
var flattab = [];
Object.keys(tab).forEach(section => {
  // console.log(section);
  tab[section] = tab[section].groupBy(r => r.Theme);
  Object.keys(tab[section]).forEach(theme => {
    // console.log(theme);
    tab[section][theme] = tab[section][theme].groupBy(r => r.Task);
    Object.keys(tab[section][theme]).forEach(task => {
      // console.log(task);
      let rows = dv.array(tab[section][theme][task]);
      let res = {};
      res['Section'] = section;
      res['Theme'] = theme;
      res['Task'] = task;
      res['Team'] = rows['Team'].distinct().sort().join();
      res['Impact'] = rows['Impact'].distinct().join("\n");
      res['Description'] = rows['Description'].distinct().join("\n");
      res['People'] = dv.array(rows['People'].join().split(", ")).distinct().sort().join();
      res['Count'] = rows['Count'].array().reduce((partialSum, a) => partialSum + a, 0);
      tab[section][theme][task] = res;
      flattab.push(res);
    })
  })
});
dv.el("pre", JSON.stringify(tab));

[blacksmithgu]

It's worth noting you can skip a step - dataview allows loading from CSV files via FROM csv(...) or await dv.io.csv(...).

[AB1908]

Wow that's gnarly. Is #555 related?