I'm in the process of creating reports for Terraform Audit logs, because of how you look up the details for each record in the AuditLog I thought the best approach would be to use a reference table and then use that in multiple lookups.
So Idea is
- Super low-level table as the reference
- A basic table that uses this reference that is set up in a manner that can use it as a template for all my other look-up tables.
- A second table references the reference table as well, this contains all the Audit log fields
This approach I thought would provide me
- The large AuditLog dataset which will only be looked up once.
- All lookup tables can quickly be created from the 2nd table (also as a reference)
- If required some reports in the dashboard can show more than just the basic fields in table2
To my surprise when I try to set up a 1-to-1 relationship between tables 2&3 I find these tables don't have the same amount of records.
What I don't understand is that if table2 and 3 are using the same reference table as their source, how can they have a different about of records
Attached is the Power query
Question is: How can 2 idenitiy tables using the same reference can have a different amount of records in them?
// dynamic_auditlog
let
Source = (page as number) =>
let
Source = Json.Document(Web.Contents("https://app.terraform.io", [Headers=[#"Content-Type"="application/vnd.api+json", Authorization="Bearer #############################################################"], RelativePath = "/api/v2/organization/audit-trail?page[number]="& Number.ToText(page) & "&page[size]=1000"])),
results = Source[data]
in
results
in
Source
// reference_table1
let
Source = List.Generate(
() => [Result = try dynamic_auditlog(1) otherwise null, pagenumber=1],
each List.IsEmpty([Result]) = false,
each [Result = try dynamic_auditlog(pagenumber) otherwise null, pagenumber = [pagenumber] + 1],
each [Result]),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandListColumn(#"Converted to Table", "Column1")
in
#"Expanded Column1"
// table2
let
Source = reference_table1,
#"Expanded Column1" = Table.ExpandRecordColumn(Source, "Column1", {"id", "resource"}, {"id", "resource"}),
#"Expanded resource" = Table.ExpandRecordColumn(#"Expanded Column1", "resource", {"id", "type"}, {"id.1", "type"})
in
#"Expanded resource"
// table3
let
Source = reference_table1,
#"Expanded Column1" = Table.ExpandRecordColumn(Source, "Column1", {"id", "resource"}, {"id", "resource"}),
#"Expanded resource" = Table.ExpandRecordColumn(#"Expanded Column1", "resource", {"id", "type"}, {"id.1", "type"})
in
#"Expanded resource"
