Power Query for Umbraco Grid Data and DTGE

queries.m

// umbracoPropertyData
let
    Source = Sql.Database(".\sqlexpress", "[your database]"),
    dbo_umbracoPropertyData = Source{[Schema="dbo",Item="umbracoPropertyData"]}[Data],
    #"Expanded cmsPropertyType" = Table.ExpandRecordColumn(dbo_umbracoPropertyData, "cmsPropertyType", {"Alias", "Name", "umbracoDataType"}, {"cmsPropertyType.Alias", "cmsPropertyType.Name", "cmsPropertyType.umbracoDataType"}),
    #"Expanded cmsPropertyType.umbracoDataType" = Table.ExpandRecordColumn(#"Expanded cmsPropertyType", "cmsPropertyType.umbracoDataType", {"propertyEditorAlias"}, {"cmsPropertyType.umbracoDataType.propertyEditorAlias"}),
    #"Expanded umbracoContentVersion" = Table.ExpandRecordColumn(#"Expanded cmsPropertyType.umbracoDataType", "umbracoContentVersion", {"id", "current", "umbracoContent"}, {"umbracoContentVersion.id", "umbracoContentVersion.current", "umbracoContentVersion.umbracoContent"}),
    #"Expanded umbracoLanguage" = Table.ExpandRecordColumn(#"Expanded umbracoContentVersion", "umbracoLanguage", {"languageISOCode", "languageCultureName"}, {"umbracoLanguage.languageISOCode", "umbracoLanguage.languageCultureName"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded umbracoLanguage", each ([umbracoContentVersion.current] = true)),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Value", each List.First(List.Select({[intValue],[decimalValue],[dateValue],[varcharValue],[textValue]}, each _ <> null))),
    #"Expanded umbracoContentVersion.umbracoContent" = Table.ExpandRecordColumn(#"Added Custom", "umbracoContentVersion.umbracoContent", {"umbracoNode"}, {"umbracoContentVersion.umbracoContent.umbracoNode"}),
    #"Expanded umbracoContentVersion.umbracoContent.umbracoNode" = Table.ExpandRecordColumn(#"Expanded umbracoContentVersion.umbracoContent", "umbracoContentVersion.umbracoContent.umbracoNode", {"id", "uniqueId", "text"}, {"umbracoNode.id", "umbracoNode.uniqueId", "umbracoNode.text"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded umbracoContentVersion.umbracoContent.umbracoNode",{"umbracoNode.id", "umbracoNode.uniqueId", "umbracoNode.text", "umbracoLanguage.languageISOCode", "umbracoLanguage.languageCultureName", "cmsPropertyType.Alias", "cmsPropertyType.Name", "cmsPropertyType.umbracoDataType.propertyEditorAlias", "Value"})
in
    #"Removed Other Columns"

// gridData
let
    Source = umbracoPropertyData,
    #"Filtered Rows" = Table.SelectRows(Source, each ([cmsPropertyType.umbracoDataType.propertyEditorAlias] = "Umbraco.Grid")),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "GridData", each Json.Document([Value])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Rows", each List.Combine(List.Transform([GridData][sections], each _[rows]))),
    #"Expanded Rows" = Table.ExpandListColumn(#"Added Custom1", "Rows"),
    #"Expanded Rows1" = Table.ExpandRecordColumn(#"Expanded Rows", "Rows", {"name", "areas", "styles", "config"}, {"Rows.name", "Rows.areas", "Rows.styles", "Rows.config"}),
    #"Expanded Rows.areas" = Table.ExpandListColumn(#"Expanded Rows1", "Rows.areas"),
    #"Expanded Rows.areas1" = Table.ExpandRecordColumn(#"Expanded Rows.areas", "Rows.areas", {"controls", "styles", "config"}, {"Rows.areas.controls", "Rows.areas.styles", "Rows.areas.config"}),
    #"Expanded Rows.areas.controls" = Table.ExpandListColumn(#"Expanded Rows.areas1", "Rows.areas.controls"),
    #"Expanded Rows.areas.controls1" = Table.ExpandRecordColumn(#"Expanded Rows.areas.controls", "Rows.areas.controls", {"value", "editor", "styles", "config"}, {"Rows.areas.controls.value", "Rows.areas.controls.editor", "Rows.areas.controls.styles", "Rows.areas.controls.config"}),
    #"Expanded Rows.areas.controls.editor" = Table.ExpandRecordColumn(#"Expanded Rows.areas.controls1", "Rows.areas.controls.editor", {"alias", "view"}, {"Rows.areas.controls.editor.alias", "Rows.areas.controls.editor.view"})
in
    #"Expanded Rows.areas.controls.editor"

// dtges
let
    Source = gridData,
    #"Filtered Rows" = Table.SelectRows(Source, each ([Rows.areas.controls.editor.view] = "/App_Plugins/DocTypeGridEditor/Views/doctypegrideditor.html")),
    #"Expanded Rows.areas.controls.value" = Table.ExpandRecordColumn(#"Filtered Rows", "Rows.areas.controls.value", {"dtgeContentTypeAlias", "value"}, {"Rows.areas.controls.value.dtgeContentTypeAlias", "Rows.areas.controls.value.value"})
in
    #"Expanded Rows.areas.controls.value"