﻿section Blackbird;

///////////////////////////////////////////////////////////////////////////////
//                      Power BI Connector Set Up                            //
///////////////////////////////////////////////////////////////////////////////
[DataSource.Kind="Blackbird", Publish="Blackbird.Publish"]
shared Blackbird.Contents = (cloud as text) as table => NavigateCalendar(cloud);

// Set the authentication method of the connector, to be an Key value, which
// we will put on the header as our Authorization token.
Blackbird = [
    Authentication = [Key = [KeyLabel = "Authorization", Key = "Token"]],
    Label = "Blackbird Authentication"
];

// Data Source UI publishing description.
Blackbird.Publish = [
    Beta = true,
    Category = "Other",
    ButtonText = { "Blackbird Connector", "Blackbird Connector" }
];


///////////////////////////////////////////////////////////////////////////////
//                             Utility Functions                             //
///////////////////////////////////////////////////////////////////////////////
// Convert a year, month, day into ISO time strings, for the API calls.
shared Blackbird.MkIsoStartTime = (year, month, day, optional hour, optional minute, optional second) => 
    year & "-" & 
    Text.PadStart(month, 2, "0") & "-" & 
    Text.PadStart(day, 2, "0") & "T" & 
    (if hour = null then "00" else Text.PadStart(hour, 2, "0")) & ":" & 
    (if minute = null then "00" else Text.PadStart(minute, 2, "0")) & ":" & 
    (if second = null then "00" else Text.PadStart(second, 2, "0")) 
    & ".000Z";
shared Blackbird.MkIsoEndTime = (year, month, day, optional hour, optional minute, optional second) => 
    year & "-" & 
    Text.PadStart(month, 2, "0") & "-" & 
    Text.PadStart(day, 2, "0") & "T" & 
    (if hour = null then "23" else Text.PadStart(hour, 2, "0")) & ":" & 
    (if minute = null then "59" else Text.PadStart(minute, 2, "0")) & ":" & 
    (if second = null then "59" else Text.PadStart(second, 2, "0")) 
    & ".000Z";

// The base URL for the data. NOTE: Add lundbeck to the URL here, if it's for Lundbeck.
shared Blackbird.MkBaseURL = (optional privateCloud as text) =>
    let
        sharedCloudUrl = "https://api.cloud.blackbird.online",
        url = if privateCloud = null or privateCloud = "shared" or privateCloud = "cloud" then sharedCloudUrl else "https://api.cloud." & privateCloud & ".blackbird.online"
    in
        url;


///////////////////////////////////////////////////////////////////////////////
//                             API Calling                                   //
///////////////////////////////////////////////////////////////////////////////
// Create a HTTP request header, for our requests.
MkHeaders = (apiKey as text) => [
    #"Accept" = "application/json;",
    #"Content-Type" = "application/json",
    #"Authorization" = apiKey
];

StripUrlEncodedSpaces = (query as text) => 
    let 
        urlEncodedQuery = Uri.EscapeDataString(query),
        firstPass = Replacer.ReplaceText(urlEncodedQuery, "%20%20", "%20"),
        secondPass = Replacer.ReplaceText(firstPass, "%20%20", "%20"),
        thirdPass = Replacer.ReplaceText(secondPass, "%20%20", "%20"),
        fourthPass = Replacer.ReplaceText(thirdPass, "%20%20", "%20")
    in
        fourthPass;

// A list of manually stiched together GraphQL queries.
Blackbird.MkGraphQLQuery = [
    // Query device samples with stats.
    Data = (uuid as text, index as text, startTime as text, endTime as text) => [
        query = StripUrlEncodedSpaces("query DeviceDataWithStats($uuid: ID!, $index: ID!, $time: [Time!]!, $points: Int) {
            device(uuid: $uuid) { 
              _id 
              sensor(index: $index) {
                  id 
                  _id 
                  index 
                  time(time: $time) { 
                      _id 
                      samples(points: $points) { 
                          data { 
                              accValue 
                              minValue
                              maxValue 
                          } 
                          timeRange { 
                              from 
                              to 
                          } 
                      } 
                      stats { 
                          data { 
                            produced 
                            longestNonStop 
                            numberOfStops 
                            averageStopLength 
                            valueAddingTime 
                            downtime 
                            averageProducedMinute 
                            averageProducedHour 
                            cycleTime 
                            producedPerStop 
                            speedWhileManned 
                            speedWhileProducing 
                          } 
                      } 
                      batches { 
                          items { 
                              id 
                              actualStart 
                              actualStop 
                          } 
                      }
                 } 
              }
            }
          }"),
        variables = StripUrlEncodedSpaces("{
          ""uuid"": """ & uuid & """,
          ""index"": """ & index & """,
          ""time"": [
            {
                ""from"": """ & startTime & """,
                ""to"": """ & endTime & """
            }
           ],
           ""points"": 500
         }")
    ],
    // Query device samples, stops, stats and batches with stats.
    FullData = (uuid as text, index as text, startTime as text, endTime as text) => [
        query = StripUrlEncodedSpaces("query FullData($uuid: ID!, $index: ID!, $time: [Time!]!, $points: Int) { 
                device(uuid: $uuid) { 
                    uuid 
                    sensor(index: $index) { 
                        id 
                        time(time: $time) { 
                            timeRange { 
                                from 
                                to 
                            } 
                            stops { 
                                timeRange { 
                                    from 
                                    to 
                                } 
                                duration 
                                comment
                                initials
                                registeredTime 
                                stopCause { 
                                    id 
                                    name 
                                    description 
                                    stopType
                                    requireInitials 
                                    categoryName 
                                } 
                            } 
                            samples(points: $points) { 
                                data { 
                                    accValue
                                    minValue 
                                    maxValue 
                                } 
                                timeRange { 
                                    from 
                                    to 
                                } 
                            }
                            stats { 
                                timeRange { 
                                    from 
                                    to 
                                } 
                                data { 
                                produced 
                                longestNonStop 
                                numberOfStops 
                                averageStopLength 
                                valueAddingTime 
                                downtime 
                                averageProducedMinute 
                                averageProducedHour 
                                cycleTime 
                                producedPerStop 
                                speedWhileManned 
                                speedWhileProducing 
                                oee { 
                                    oee1 
                                    oee2 
                                    oee3 
                                    tcu 
                                    totalEquipmentTime 
                                    mannedTime 
                                    productionTime 
                                    operatingTime 
                                    valuedOperatingTime 
                                    speedLoss 
                                    scrapLoss 
                                } 
                            } 
                        } 
                        batches { 
                            items { 
                                batchId 
                                batchNumber 
                                amount 
                                plannedStart 
                                actualStart 
                                actualStop 
                                comment 
                                product { 
                                    productId 
                                    name 
                                    itemNumber 
                                    validatedLineSpeed 
                                    expectedAverageSpeed 
                                    dataMultiplier 
                                    comment 
                                    packaging { 
                                        packagingId 
                                        name 
                                        unit 
                                        comment 
                                    } 
                                } 
                                stats { 
                                    data { 
                                        produced 
                                    } 
                                } 
                            } 
                        } 
                    } 
                } 
            } 
        }"),
        variables = StripUrlEncodedSpaces("{
          ""uuid"": """ & uuid & """,
          ""index"": """ & index & """,
          ""time"": [
            {
                ""from"": """ & startTime & """,
                ""to"": """ & endTime & """
            }
           ],
           ""points"": 500
         }")
    ],
    DeviceAndLinesList = () => [
        query = StripUrlEncodedSpaces("query devicesSimple {
          devices {
            uuid
            sensors {
                index
                name
                description
            }
          }
          lines {
            id
            name
            description
          }
        }"),
        variables = StripUrlEncodedSpaces("{}")
    ],
    LinesOEE = (lineId as text, startTime as text, endTime as text) => [
        query = StripUrlEncodedSpaces("query lineOEEData($lineId: ID!, $time: [Time!]!, $includeScrap: Boolean = true) {
          line(lineId: $lineId) {
            id
            time(time: $time) {
                timeRange {
                from
                to
                }
                stops {
                timeRange {
                    from
                    to
                }
                duration
                stopCause {
                    stopType
                    name
                    description
                }
                }
                stats {
                timeRange {
                    from
                    to
                }
                data {
                    oee(includeScrap: $includeScrap) {
                    oee1
                    oee2
                    oee3
                    tcu
                    totalEquipmentTime
                    mannedTime
                    productionTime
                    operatingTime
                    valuedOperatingTime
                    speedLoss
                    scrapLoss
                    }
                }
                }
            }
          }
        }"),
        variables = StripUrlEncodedSpaces("{
          ""lineId"": """ & lineId & """,
          ""time"": [
            {
                ""from"": """ & startTime & """,
                ""to"": """ & endTime & """
            }
           ]
         }")
    ]
];

// Send a GET request, with an API token, to the Blackbird API.
Blackbird.SendGETRequest = (apiKey as text, url as text) =>
    let
        source = Web.Contents(
            url,
            [ Headers = MkHeaders(apiKey) ]
        ),
        json = Json.Document(source)
    in
        json;

// Query device and line lists.
shared Blackbird.QueryList = (apiKey, optional privateCloud as text) =>
    let
        query = Blackbird.MkGraphQLQuery[DeviceAndLinesList](),
        url = Blackbird.MkBaseURL(privateCloud) & "?query=" & query[query] & "&variables=" & query[variables],
        json = Blackbird.SendGETRequest(apiKey, url)
    in
        json;

// Query devices related queries.
shared Blackbird.QueryDevice = (apiKey, uuid as text, index as text, startTime as text, endTime as text, optional privateCloud as text) =>
    let
        queryItem = Blackbird.MkGraphQLQuery[FullData],
        query = queryItem(uuid, index, startTime, endTime),
        url = Blackbird.MkBaseURL(privateCloud) & "?query=" & query[query] & "&variables=" & query[variables],
        json = Blackbird.SendGETRequest(apiKey, url)
    in
        json;

// Query line related queries.
shared Blackbird.QueryLine = (apiKey, lineId as text, startTime as text, endTime as text, optional privateCloud as text) =>
    let
        queryItem = Blackbird.MkGraphQLQuery[LinesOEE],
        query = queryItem(lineId, startTime, endTime),
        url = Blackbird.MkBaseURL(privateCloud) & "?query=" & query[query] & "&variables=" & query[variables],
        json = Blackbird.SendGETRequest(apiKey, url)
    in
        json;


///////////////////////////////////////////////////////////////////////////////
//                    Transforming Data Into Tables                          //
///////////////////////////////////////////////////////////////////////////////
// Convert our raw JSON into a table of the samples time-series data.
shared Blackbird.TransformIntoSamplesTable = (rawData) as table =>
    let
        data = rawData[data],
        device = data[device],
        sensor = device[sensor],
        time = sensor[time],
        time1 = time{0},
        samples = time1[samples],
        // Handle an empty table.
        samplesTable = if List.IsEmpty(samples)
            then #table({"accValue", "minValue", "maxValue", "from", "to"}, {})
            else
                let
                    #"Converted to Table" = Table.FromList(samples, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
                    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"data", "timeRange"}, {"data", "timeRange"}),
                    #"Expanded timeRange" = Table.ExpandRecordColumn(#"Expanded Column1", "timeRange", {"from", "to"}, {"from", "to"}),
                    #"Expanded data" = Table.ExpandRecordColumn(#"Expanded timeRange", "data", {"accValue", "minValue", "maxValue"}, {"accValue", "minValue", "maxValue"}),
                    #"Changed Type" = Table.TransformColumnTypes(#"Expanded data",{{"from", type datetimezone}, {"to", type datetimezone}, {"maxValue", type number}, {"minValue", type number}, {"accValue", type number}})
                in
                    #"Changed Type"
        in
            samplesTable;

// Convert our raw JSON into a table of stops data.
shared Blackbird.TransformIntoStopsTable = (rawData) as table =>
    let
        data = rawData[data],
        device = data[device],
        sensor = device[sensor],
        time = sensor[time],
        time1 = time{0},
        stops = time1[stops],
        // Handle an empty table.
        stopsTable = if List.IsEmpty(stops)
            then #table({"from", "to", "duration", "comment", "initials", "registeredTime.from", "registeredTime.to", "stopCause"}, {})
            else
                let
                    #"Converted to Table" = Table.FromList(stops, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
                    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"timeRange", "duration", "comment", "initials", "registeredTime", "stopCause"}, {"timeRange", "duration", "comment", "initials", "registeredTime", "stopCause"}),
                    #"Expanded timeRange" = Table.ExpandRecordColumn(#"Expanded Column1", "timeRange", {"from", "to"}, {"from", "to"}),
                    #"Changed Type" = Table.TransformColumnTypes(#"Expanded timeRange",{{"from", type datetimezone}, {"to", type datetimezone}, {"duration", Int64.Type}, {"comment", type text}, {"initials", type text}, {"registeredTime", type datetimezone}}),
                    #"Expanded stopCause" = Table.ExpandRecordColumn(#"Expanded timeRange", "stopCause", {"id", "name", "description", "stopType", "requireInitials", "categoryName"}, {"stopCause.id", "stopCause.name", "stopCause.description", "stopCause.stopType", "stopCause.requireInitials", "stopCause.categoryName"}),
                    #"Changed stopCause Type" = Table.TransformColumnTypes(#"Expanded stopCause",{{"stopCause.id", type text}, {"stopCause.name", type text}, {"stopCause.description", type text}, {"stopCause.stopType", type text}, {"stopCause.requireInitials", type logical}, {"stopCause.categoryName", type text}, {"registeredTime", type datetimezone}}),
                    #"Rounded Off duration" = Table.TransformColumns(#"Changed stopCause Type", {{"duration", each #duration(0,0,0, _ / 1000), type number}}),
                    #"Changed duration Type" = Table.TransformColumnTypes(#"Rounded Off duration",{{"duration", type duration}}),
                    #"Set null as unregistered" = Table.TransformColumns(#"Changed duration Type", {{"stopCause.stopType", each if _ = null then "UNREGISTERED" else _, type text}})
                in
                    #"Set null as unregistered"
        in
            stopsTable;

// Convert our raw JSON into a table of the statistic KPIs.
shared Blackbird.TransformIntoStatsTable = (rawData) as table =>
    let
        data = rawData[data],
        device = data[device],
        sensor = device[sensor],
        time = sensor[time],
        time1 = time{0},
        stats = time1[stats],
        statsData = stats[data],
        #"Converted to Table" = Record.ToTable(statsData),
        #"Filtered Rows" = Table.SelectRows(#"Converted to Table", each ([Name] <> "__typename" and [Name] <> "oee")),
        #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Value", type number}})
    in
        #"Changed Type";

// Convert our raw JSON into a table of batches.
shared Blackbird.TransformIntoBatchesTable = (rawData) as table =>
    let
        data = rawData[data],
        device = data[device],
        sensor = device[sensor],
        time = sensor[time],
        time1 = time{0},
        batches = time1[batches],
        items = batches[items],
        // Handle an empty table.
        batchTable = if List.IsEmpty(items)
            then #table({"id", "batchNumber", "amount", "plannedStart", "actualStart", "actualStop", "comment", "product.id", "product.name", "product.itemNumber", "product.validatedLineSpeed", "product.expectedAverageSpeed", "product.dataMultiplier", "product.comment", "product.packaging.id", "product.packaging.name", "product.packaging.unit", "product.packaging.comment"}, {})
            else
                let
                    batches1 = items{0},
                    #"Converted to Table" = Record.ToTable(batches1),
                    #"Transposed Table" = Table.Transpose(#"Converted to Table"),
                    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
                    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"batchId", type text}, {"batchNumber", type text}, {"amount", Int64.Type}, {"plannedStart", type datetime}, {"actualStart", type datetime}, {"actualStop", type any}, {"comment", type text}, {"product", type any}, {"stats", type any}}),
                    #"Expanded product" = Table.ExpandRecordColumn(#"Changed Type", "product", {"productId", "name", "itemNumber", "validatedLineSpeed", "expectedAverageSpeed", "dataMultiplier", "comment", "packaging"}, {"product.productId", "product.name", "product.itemNumber", "product.validatedLineSpeed", "product.expectedAverageSpeed", "product.dataMultiplier", "product.comment", "product.packaging"}),
                    #"Expanded product.packaging" = Table.ExpandRecordColumn(#"Expanded product", "product.packaging", {"packagingId", "name", "unit", "comment"}, {"product.packaging.packagingId", "product.packaging.name", "product.packaging.unit", "product.packaging.comment"}),
                    #"Expanded stats" = Table.ExpandRecordColumn(#"Expanded product.packaging", "stats", {"data"}, {"data"}),
                    #"Expanded data" = Table.ExpandRecordColumn(#"Expanded stats", "data", {"produced"}, {"produced"}),
                    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded data",{{"amount", type number}, {"plannedStart", type datetimezone}, {"actualStart", type datetimezone}, {"actualStop", type datetimezone}, {"product.productId", type text}, {"batchId", type text}, {"product.name", type text}, {"product.itemNumber", type text}, {"product.validatedLineSpeed", type number}, {"product.expectedAverageSpeed", type number}, {"product.dataMultiplier", type number}, {"product.comment", type text}, {"product.packaging.packagingId", type text}, {"product.packaging.name", type text}, {"product.packaging.unit", type text}, {"product.packaging.comment", type text}, {"produced", type number}})
                in
                    #"Changed Type1"
    in
        batchTable;

// Convert our raw JSON into a table of the stats OEE KPIs.
shared Blackbird.TransformIntoOEETable = (rawData) as table =>
    let
        data = rawData[data],
        device = data[device],
        sensor = device[sensor],
        time = sensor[time],
        time1 = time{0},
        stats = time1[stats],
        oeeData = stats[data],
        oee = oeeData[oee],
        oeeTable = if oee = null
            then
                #table({"Name", "Value"}, {})
            else
                let
                    #"Converted to Table" = Record.ToTable(oee),
                    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Value", type number}})
                in
                    #"Changed Type"
    in
        oeeTable;

// Convert our raw JSON into a table of the Line OEE KPIs.
shared Blackbird.TransformIntoLineOEETable = (rawData) as table =>
    let
        data = rawData[data],
        line = data[line],
        time = line[time],
        time1 = time{0},
        stats = time1[stats],
        oeeData = stats[data],
        oee = oeeData[oee],
        oeeTable = if oee = null
            then
                #table({"Name", "Value"}, {})
            else
                let
                    #"Converted to Table" = Record.ToTable(oee),
                    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Value", type number}})
                in
                    #"Changed Type"
    in
        oeeTable;


///////////////////////////////////////////////////////////////////////////////
//                           Navigational Tables                             //
///////////////////////////////////////////////////////////////////////////////
// Create a calendar navigation table, for selecting year, month, day, etc.
NavigateCalendar = (optional privateCloud as text) as table =>
    let
        // Figure out the correct number of days in a month, taking leap years into account.
        numberOfDays = (year, month) =>
            let
                daysInMonths = [
                    #"1" = (year) => 31,
                    #"2" = (year) => if Date.IsLeapYear(DateTime.FromText(year & "-02-01")) then 29 else 28,
                    #"3" = (year) => 31,
                    #"4" = (year) => 30,
                    #"5" = (year) => 31,
                    #"6" = (year) => 30,
                    #"7" = (year) => 31,
                    #"8" = (year) => 31,
                    #"9" = (year) => 30,
                    #"10" = (year) => 31,
                    #"11" = (year) => 30,
                    #"12" = (year) => 31
                ]
            in
                Record.Field(daysInMonths, month)(year),
        // Create a navigation table for the days, which expands into the devices/lines lists.
        dayNavTable = (year, month) =>
            let
                listOfDays = List.Range({1..numberOfDays(year, month)}, 0),
                days = List.Transform(listOfDays, each { Text.PadStart(Number.ToText(_), 2, "0"), _, month, year }),
                daySource = #table({"Name", "Day", "Month", "Year"}, days),
                daysWithData = Table.AddColumn(daySource, "Data", each NavigateDevicesAndLines(Blackbird.MkIsoStartTime(year, month, Number.ToText(_[Day])), Blackbird.MkIsoEndTime(year, month, Number.ToText(_[Day])), privateCloud), type table),
                // We add an entry for the full month.
                lastDayInMonth = List.Last(listOfDays),
                daysWithDataAndFullMonth = Table.InsertRows(daysWithData, 0, {
                    [Name = "00 Full Month", Day = "1-" & Number.ToText(lastDayInMonth), Month = month, Year = year, Data = NavigateDevicesAndLines(Blackbird.MkIsoStartTime(year, month, "1"), Blackbird.MkIsoEndTime(year, month, Number.ToText(lastDayInMonth)), privateCloud)]
                }),
                navTable = Table.ToNavigationTable(daysWithDataAndFullMonth, {"Name"}, "Name", "Data")
            in
                navTable,
        // Create a navigation table for the months, which expand into a list of days.
        monthNavTable = (year) =>
            let
                nextYear = Number.ToText(Number.FromText(year) + 1),
                monthSource = #table({"Name", "Month", "Year", "Data"}, {
                    { "01 January", "1", year, dayNavTable(year, "1") },
                    { "02 February", "2", year, dayNavTable(year, "2") },
                    { "03 March", "3", year, dayNavTable(year, "3") },
                    { "04 April", "4", year, dayNavTable(year, "4") },
                    { "05 May", "5", year, dayNavTable(year, "5") },
                    { "06 June", "6", year, dayNavTable(year, "6") },
                    { "07 July", "7", year, dayNavTable(year, "7") },
                    { "08 August", "8", year, dayNavTable(year, "8") },
                    { "09 September", "9", year, dayNavTable(year, "9") },
                    { "10 October", "10", year, dayNavTable(year, "10") },
                    { "11 November", "11", year, dayNavTable(year, "11") },
                    { "12 December", "12", year, dayNavTable(year, "12") },
                    { "Q1", "1-3", year, NavigateDevicesAndLines(Blackbird.MkIsoStartTime(year, "1", "1"), Blackbird.MkIsoEndTime(year, "3", "31")) },
                    { "Q2", "4-6", year, NavigateDevicesAndLines(Blackbird.MkIsoStartTime(year, "4", "1"), Blackbird.MkIsoEndTime(year, "6", "30")) },
                    { "Q3", "7-9", year, NavigateDevicesAndLines(Blackbird.MkIsoStartTime(year, "7", "1"), Blackbird.MkIsoEndTime(year, "9", "30")) },
                    { "Q4", "10-12", year, NavigateDevicesAndLines(Blackbird.MkIsoStartTime(year, "10", "1"), Blackbird.MkIsoEndTime(year, "12", "31")) },
                    { "Q1 & Q2", "1-6", year, NavigateDevicesAndLines(Blackbird.MkIsoStartTime(year, "1", "1"), Blackbird.MkIsoEndTime(year, "6", "30")) },
                    { "Q2 & Q3", "4-9", year, NavigateDevicesAndLines(Blackbird.MkIsoStartTime(year, "4", "1"), Blackbird.MkIsoEndTime(year, "9", "30")) },
                    { "Q3 & Q4", "7-12", year, NavigateDevicesAndLines(Blackbird.MkIsoStartTime(year, "7", "1"), Blackbird.MkIsoEndTime(year, "12", "31")) },
                    { "All year", "1-12", year, NavigateDevicesAndLines(Blackbird.MkIsoStartTime(year, "1", "1"), Blackbird.MkIsoEndTime(year, "12", "31")) },
                    { "Q4 & Q1 Next Year", "10-3", year & " & " & nextYear, NavigateDevicesAndLines(Blackbird.MkIsoStartTime(year, "10", "1"), Blackbird.MkIsoEndTime(nextYear, "3", "31"), privateCloud) }
                }),
                navTable = Table.ToNavigationTable(monthSource, {"Month"}, "Name", "Data")
            in
                navTable,
        // Figure out the current year, so we can iterate from a start year up until the current year.
        currentDate = DateTime.LocalNow(),
        currentYear = Date.Year(currentDate),
        listOfYears = List.Range({2019..currentYear}, 0),
        // Stitch the year list together with the months table, and create a navigation from this.
        years = List.Transform(listOfYears, each {_,  monthNavTable(Number.ToText(_))}),
        source = #table({"Name", "Data"}, years),
        sortedSource = Table.Sort(source, {{"Name", Order.Descending}, "Name"}),
        navTable = Table.ToNavigationTable(sortedSource, {"Name"}, "Name", "Data")
    in
        navTable;

// Create a navigation overview of all lines and devices.
NavigateDevicesAndLines = (startTime as text, endTime as text, optional privateCloud as text) =>
    let
        apiKey = Extension.CurrentCredential()[Key],
        devicesAndLinesList = Blackbird.QueryList(apiKey, privateCloud),
        devices = devicesAndLinesList[data][devices],
        lines = devicesAndLinesList[data][lines],

        // Loop through the sensors of each device, and add the device UUID to it.
        sensorsLists = List.Transform(devices, each [deviceUuid = _[uuid], deviceSensors = List.Transform(_[sensors], each [uuid = deviceUuid, index = _[index], Name = _[name], description = _[description]])][deviceSensors]),
        // Merge all the lists of sensors into one list.
        mergedSensors = List.Union(sensorsLists),

        devicesTable = Table.FromRecords(mergedSensors),
        linesTable = Table.FromRecords(List.Transform(lines, each [id = _[id], Name = _[name], description = _[description]])),

        // Create the data tables (samples, stops, etc) for each device.
        devicesWithData = Table.AddColumn(devicesTable, "Data", each NavigateDevice(_, startTime, endTime, privateCloud), type table),
        // Create the data tables (samples, stops, etc) for each device.
        linesWithData = Table.AddColumn(linesTable, "Data", each NavigateLine(_, startTime, endTime, privateCloud), type table),

        // Set up the navigation tables for the devices and lines.
        devicesNavigationTable = Table.ToNavigationTable(devicesWithData, {"uuid", "index"}, "Name", "Data"),
        linesNavigationTable = Table.ToNavigationTable(linesWithData, {"id"}, "Name", "Data"),
        source = #table({"Name", "Data"}, {
            { "Devices", devicesNavigationTable },
            { "Lines", linesNavigationTable }
        }),
        navTable = Table.ToNavigationTable(source, {"Name"}, "Name", "Data")
    in
        navTable;

// Create a table to navigate the specific line data you are interested in.
NavigateLine = (record as record, startTime as text, endTime as text, optional privateCloud as text) =>
    let
        // Pull out all the data in one go, and construct the tables from this.
        apiKey = Extension.CurrentCredential()[Key],
        rawData = Blackbird.QueryLine(apiKey, record[id], startTime, endTime, privateCloud),
        source = #table({"Name", "Data"}, {
            { "Information", #table({"LineId", "Name", "Description", "Start Time", "End Time"}, {{record[id], record[Name], record[description], startTime, endTime}}) },
            { "OEE", Blackbird.TransformIntoLineOEETable(rawData) },
            { "Raw",  Table.FromRecords({rawData[data][line]}) }
        }),
        navTable = Table.ToNavigationTable(source, {"Name"}, "Name", "Data")
    in
        navTable;

// Create a table to navigate the specific device data you are interested in.
NavigateDevice = (record as record, startTime as text, endTime as text, optional privateCloud as text) =>
    let
        // Pull out all the data in one go, and construct the tables from this.
        apiKey = Extension.CurrentCredential()[Key],
        rawData = Blackbird.QueryDevice(apiKey, record[uuid], record[index], startTime, endTime, privateCloud),
        data = rawData[data],
        device = data[device],
        sensor = device[sensor],
        source = #table({"Name", "Data"}, {
            { "Information", #table({"UUID", "Index", "Name", "Description", "Start Time", "End Time"}, {{record[uuid], record[index], record[Name], record[description], startTime, endTime}}) },
            { "Samples", Blackbird.TransformIntoSamplesTable(rawData) },
            { "Stops", Blackbird.TransformIntoStopsTable(rawData) },
            { "Batches", Blackbird.TransformIntoBatchesTable(rawData) },
            { "KPIs", Blackbird.TransformIntoStatsTable(rawData) },
            { "OEE", Blackbird.TransformIntoOEETable(rawData) },
            { "Raw",  Table.FromRecords({rawData[data][device]}) }
        }),
        navTable = Table.ToNavigationTable(source, {"Name"}, "Name", "Data")
    in
        navTable;

// Convert a list of records into a table with the correct data types.
Table.ToNavigationTable = (
    table as table,
    keyColumns as list,
    nameColumn as text,
    dataColumn as text
) as table =>
    let
        tableType = Value.Type(table),
        newTableType = Type.AddTableKey(tableType, keyColumns, true) meta
        [NavigationTable.NameColumn = nameColumn, NavigationTable.DataColumn = dataColumn],
        navigationTable = Value.ReplaceType(table, newTableType)
    in
        navigationTable;
