基于Power Query的M代码实现Notion JSON数据扁平化以搭建仪表盘

免责声明 #

我正在寻找用M代码扁平化JSON对象的方法，也清楚可能存在更优方案，因此提供相关背景并接受其他思路。我花费4小时准备测试数据库和整理代码，感谢您的帮助。

背景 #

我喜爱Notion，但它缺乏强大的图表工具，目前的解决方案是手动导出CSV，经Excel和Power Query转换后上传至可视化平台。

总体目标 #

实现Notion数据自动更新为美观图表并嵌入Notion，同时也接受其他方案（已尝试Google Looker Studio，效果不佳）。

当前问题 #

Notion支持手动导出CSV，但操作繁琐；其API功能强大，但返回的深层嵌套JSON难以通过Power Query扁平化为可用的类CSV结构。

进展情况 #

• 已编写M代码函数QueryDatabaseFromNotion(databaseId as text, apiSecretKey as text)，可通过databaseId和APIKey获取JSON数据并导入Power Query。
• 已编写自定义函数ConvertJSONBranchToCSV(DataTable as table, ColumnName as text)，可将多数属性转换为原始值，但存在两个限制：
  • 限制1：需逐个应用到列，无法批量处理（我有6个数据库，每个最多80列）
  • 限制2：不知如何将一对多属性转换为逗号分隔的字符串（见代码中"TYPE 4. COMPLEX:"部分）

核心问题 #

1. 为Notion搭建强大图表，当前路径是否正确？
2. 若路径可行，能否解决上述两个限制问题？

相关M代码 #

let 
    getRawValueFromJsonTree = (DataTable as table, ColumnName as text) =>
        let
            #"output" = 
                let 
                // =========== STEP 1. 若属性为'rollup'或'formula'，则深入一层 =====================================================================================================================
                    // 'rollup'或'formula'类型的列可能包含其他类型数据，此步骤用于深入一层，确保后续代码正常运行。
                    // 示例：数值类型属性路径 = results{0}.properties.标题列名.number     
                    // 示例：公式返回数值的属性路径 = results{0}.properties.标题列名.formula.number
                    intialTypeStr = 
                        try 
                            // 假设第一条记录的数据类型适用于所有记录（注：icon和cover属性不适用此假设）
                            Table.ExpandRecordColumn(DataTable, ColumnName, {"type"})[type]{0}
                        catch(err) => 
                            if err[Message] <> null then "Error: Already Raw Data" else "Pass(This is never shown)",
                            
                    typeStr = 
                        if intialTypeStr = "rollup" then
                            // 注意：若API密钥无关联数据库权限，Rollup计算结果将全部为null
                            Table.ExpandRecordColumn(DataTable, ColumnName, {"rollup"})[rollup]{0}[type]
                        else if intialTypeStr = "formula" then
                            Table.ExpandRecordColumn(DataTable, ColumnName, {"formula"})[formula]{0}[type]                       
                        else if intialTypeStr = null then
                            "Error: No Type Property"
                        else
                            intialTypeStr,
                            
                    #"BaseLevel" =
                        if intialTypeStr = "formula" then
                            Table.ExpandRecordColumn(DataTable, ColumnName, {"formula"}, {ColumnName})
                        else if intialTypeStr = "rollup" then
                            Table.ExpandRecordColumn(DataTable, ColumnName, {"rollup"}, {ColumnName})
                        else
                           DataTable
                in
                // =========== STEP 2. 从JSON结构中提取原始值 ======================================================================================================================================
                    if typeStr = "Error: Already Raw Data" then
                    // =========== TYPE 1. 简单场景：已为原始数据，无需修改   ==================================================================================================================================
                        #"BaseLevel" // 列已为原始类型，无需更改
                    else if typeStr =  "Error: No Type Property" then
                        // 此情况适用于所有数据库默认属性，例如"created_by"（注：此类属性实际未存储在JSON的'properties'下）
                        Table.ExpandRecordColumn(DataTable, ColumnName, {"id"}, {ColumnName})
                    // =========== TYPE 2. 简单场景：仅需一层提取的属性类型 ====================================================================================================
                    // 先处理简单类型：
                    // 示例：数值类型属性路径 = results{0}.properties.标题列名.number
                    // 示例：字符串类型属性路径 = results{0}.properties.标题列名.string
                    else if 
                        typeStr = "checkbox"
                        or typeStr = "boolean" // 此类型仅出现于公式返回复选框结果的情况
                        or typeStr = "string"
                        or typeStr = "email"
                        or typeStr = "number"
                        or typeStr = "phone_number"
                        or typeStr = "url"
                        or typeStr = "created_time"
                        or typeStr = "last_edited_time"      
                        or typeStr = "database_id"                     
                    then
                        Table.ExpandRecordColumn(#"BaseLevel", ColumnName, {typeStr}, {ColumnName})
                    // ELSE
                    // =========== TYPE 3. 中等场景：需按特定路径提取的属性类型 =======================================================================================================
                    // 处理多层嵌套的复杂类型：
                    // 示例：标题类型属性路径 = results{0}.properties.标题列名.title{0}.plain_text
                    else if typeStr = "title" then
                        let 
                            #"A" = Table.ExpandRecordColumn(#"BaseLevel", ColumnName, {typeStr}, {ColumnName}),
                            #"B" = Table.ExpandListColumn(#"A", ColumnName),
                            #"C" = Table.ExpandRecordColumn(#"B", ColumnName, {"plain_text"}, {ColumnName})            
                        in
                            #"C"
                    else if 
                        typeStr = "status"
                        or typeStr = "select"
                    then
                        let 
                            #"A" = Table.ExpandRecordColumn(#"BaseLevel", ColumnName, {typeStr}, {ColumnName}),
                            #"B" = Table.ExpandRecordColumn(#"A", ColumnName, {"name"}, {ColumnName})
                        in
                            #"B"
                    else if typeStr = "unique_id" then
                        let 
                            #"A" = Table.ExpandRecordColumn(#"BaseLevel", ColumnName, {typeStr}, {ColumnName}),
                            #"B" = Table.ExpandRecordColumn(#"A", ColumnName, {"number"}, {ColumnName})
                        in
                            #"B"
                    else if typeStr = "rich_text" then
                        let 
                            #"A" = Table.ExpandRecordColumn(#"BaseLevel", ColumnName, {typeStr}, {ColumnName}),
                            #"B" = Table.ExpandListColumn(#"A", ColumnName),
                            #"C" = Table.ExpandRecordColumn(#"B", ColumnName, {"plain_text"}, {ColumnName})
                        in
                            #"C"
                    else if typeStr = "date" then
                        let 
                            #"A" = Table.ExpandRecordColumn(#"BaseLevel", ColumnName, {typeStr}, {ColumnName}),
                            #"START" = Table.ExpandRecordColumn(#"A", ColumnName, {"start"}, {ColumnName}),
                            #"END" = Table.ExpandRecordColumn(#"A", ColumnName, {"end"}, {ColumnName}),
                            // 注：若start或end不包含时间，日期格式将不符合标准yyyy-mm-ddThh:mm:ss.mmmm+{时区偏移}
                            //#"B" = Text.Combine({Text.From(#"START"), Text.From(#"END")}, " to ") // 目前暂忽略END字段
                            #"B" = #"START"                            
                        in
                            #"B"
                    else if 
                        typeStr = "created_by" 
                        or typeStr = "last_edited_by"
                    then
                        let 
                            #"A" = Table.ExpandRecordColumn(#"BaseLevel", ColumnName, {typeStr}, {ColumnName}),
                            #"B" = Table.ExpandRecordColumn(#"A", ColumnName, {"id"}, {ColumnName})
                        in
                            #"B"
                    // =========== TYPE 4. 复杂场景：一对多关系的属性类型提取 =========================================================================================================
                    else if typeStr = "array" then
                        // TODO：优化此处逻辑，Rollup的数组结构路径较为复杂，与上方"title"属性存在重叠
                        // 路径示例：results{0}.properties.标题列名.rollup.array.{[{List_Object!}]}.title}.{[{List_Object!}]}.plain_text
                        // results{0}.properties.标题列名.rollup.array.{[{List_Object!}]}.{GetType()}.{[{List_Object!}]}.plain_text
                        // results{0}.properties.标题列名.rollup.array.{[{List_Object!}]}.{GetType()}.{[{List_Object!}]}.{GetType()}.content

                        let 
                            #"A" = Table.ExpandRecordColumn(#"BaseLevel", ColumnName, {typeStr}, {ColumnName}),
                            #"B" = Table.ExpandListColumn(#"A", ColumnName),     // 暂不使用，此操作会生成多行数据，不符合需求
                            //#"C" = Table.ExpandRecordColumn(#"B", ColumnName, {"id"}, {ColumnName})
                            //#"C" = Table.ExpandRecordColumn(#"B", ColumnName, {"plain_text"}, {ColumnName})
                            rollupTypeStr = Table.ExpandRecordColumn(#"B", ColumnName, {"type"}){0}[type],
                            #"DigBasedOnRollupType" =  if rollupTypeStr = "title" then
                                // 适用于Rollup类型 = show_original
                                let 
                                    #"W" = #"B",
                                    #"X" = Table.ExpandRecordColumn(#"W", ColumnName, {rollupTypeStr}, {ColumnName}),
                                    #"Y" = Table.ExpandListColumn(#"X", ColumnName),      // 暂不使用，此操作会生成多行数据，不符合需求
                                    #"Z" = Table.ExpandRecordColumn(#"Y", ColumnName, {"plain_text"}, {ColumnName})            
                                in
                                    //#"Z"
                                    #"X"
                            else if rollupTypeStr = null then 
                                // 适用于Rollups类型 = Sum
                                Table.ExpandRecordColumn(#"B", ColumnName, {"id"}, {ColumnName})
                            else
                                //#"B"
                                rollupTypeStr
                        in                            
                            //#"DigBasedOnRollupType"
                            #"A"
                    else if typeStr = "relation" then
                        // TODO：此类型包含'has_more'属性，推测当关联记录超过100条时需要处理分页逻辑
                        let 
                            #"A" = Table.ExpandRecordColumn(#"BaseLevel", ColumnName, {typeStr}, {ColumnName}),
                            #"B" = Table.ExpandListColumn(#"A", ColumnName),     // 暂不使用，此操作会生成多行数据，不符合需求
                            #"C" = Table.ExpandRecordColumn(#"B", ColumnName, {"id"}, {ColumnName})
                        in
                            //#"C"
                            #"A"
                    else if typeStr = "files" then
                        let 
                            #"A" = Table.ExpandRecordColumn(#"BaseLevel", ColumnName, {typeStr}, {ColumnName}),
                            #"B" = Table.ExpandListColumn(#"A", ColumnName)     // 暂不使用，此操作会生成多行数据，不符合需求
                        in
                            #"A"
                    else if typeStr = "multi_select" then
                        let 
                            #"A" = Table.ExpandRecordColumn(#"BaseLevel", ColumnName, {typeStr}, {ColumnName}),
                            #"B" = Table.ExpandListColumn(#"A", ColumnName)     // 暂不使用，此操作会生成多行数据，不符合需求
                        in
                            #"A"
                    else if typeStr = "relation" then
                        #"BaseLevel" // 暂未找到有效处理方式
                    else if typeStr = "people" then
                        let 
                            #"A" = Table.ExpandRecordColumn(#"BaseLevel", ColumnName, {typeStr}, {ColumnName}),
                            #"B" = Table.ExpandListColumn(#"A", ColumnName)     // 暂不使用，此操作会生成多行数据，不符合需求
                        in
                            #"A"
                    else
                        #"BaseLevel" // TODO：若遇到未识别的列类型，应向用户提示错误而非直接跳过
            in
                #"output"
    in 
        getRawValueFromJsonTree

内容的提问来源于stack exchange，提问作者Logan