Forum Discussion

tessahurr's avatar
tessahurr
Icon for Microsoft Employee rankMicrosoft Employee
5 years ago

Share your thoughts on DirectQuery for Power BI datasets and Azure Analysis Services (preview)

Hit Reply and let us know what you think of the DirectQuery for Power BI datasets and Azure Analysis Services.  To learn more about this feature, please visit this blog post or our documentation.   ...
General Comment
Anonymous's avatar
Anonymous
Not applicable
4 years ago

Looks like DQ is importing calculated tables (not normal tables) flagged as private. This is irrespective of the "Include tables added later" checkbox. I noticed this after adding some clustering via the standard Scatter Chart visual to one of the datasets.

 

I imagine this would be an issue for those who use private tables to track things - like the dataset template version that I've just started tracking using a private table 😞

 

The below images show the tables selected for import (not the private tables are not listed), and the ClusterMappingTable tables that ended up in the model.

 

 

 

jeroenterheerdt's avatar
jeroenterheerdt
Icon for Microsoft Employee rankMicrosoft Employee
4 years ago

thanks for reporting this - I will look into this!

  • jeroenterheerdt's avatar
    jeroenterheerdt
    Icon for Microsoft Employee rankMicrosoft Employee
    4 years ago

    Anonymous it took me a while to get back to you as we are unable to reproduce this problem. Are you still seeing this issue?

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Been a while since I checked. I'll confirm when I'm back in the office on Monday. I ended up manually modifying the json annotation that controls the schema import using TE3; to remove the tables I didn't want included. 😊 (And haven't made any subsequent changes since...)

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      jeroenterheerdt 

       

      Sorry for the delay in getting back to you. Yes, this is still a problem.

       

      I created an example for you. SourceModel.bim which is used as the source model for the Desktop DQ for AS connection as shown in the below snips.

       

      I also now note that it looks like it only happens when a table is both flagged as private and hidden.

       

      SourceModel.bim as Json:

      Spoiler
      {
  "name": "SourceModel",
  "compatibilityLevel": 1550,
  "model": {
    "culture": "en-NZ",
    "dataAccessOptions": {
      "legacyRedirects": true,
      "returnErrorValuesAsNull": true
    },
    "defaultPowerBIDataSourceVersion": "powerBI_V3",
    "sourceQueryCulture": "en-NZ",
    "tables": [
      {
        "name": "PqTable",
        "lineageTag": "7001ad32-dbd3-4728-a2da-095a886fb709",
        "columns": [
          {
            "name": "Column1",
            "dataType": "string",
            "sourceColumn": "Column1",
            "lineageTag": "aab2170a-ae38-4eec-8765-32a2cdab3d7c",
            "summarizeBy": "none",
            "annotations": [
              {
                "name": "SummarizationSetBy",
                "value": "Automatic"
              }
            ]
          }
        ],
        "partitions": [
          {
            "name": "PqTable-2446d46a-8837-4b4f-b324-f8cb57d49656",
            "mode": "import",
            "source": {
              "type": "m",
              "expression": [
                "let",
                "    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(\"i45W8ssvycjMSw/JD05N9UgtSlWKjQUA\", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),",
                "    #\"Changed Type\" = Table.TransformColumnTypes(Source,{{\"Column1\", type text}})",
                "in",
                "    #\"Changed Type\""
              ]
            }
          }
        ],
        "annotations": [
          {
            "name": "PBI_ResultType",
            "value": "Table"
          }
        ]
      },
      {
        "name": "PqTablePrivate",
        "isPrivate": true,
        "lineageTag": "a3264cb3-17b2-45e0-9628-8f916380f694",
        "columns": [
          {
            "name": "Column1",
            "dataType": "string",
            "sourceColumn": "Column1",
            "lineageTag": "3ff1f0ad-f03b-4861-8fd5-674c27991c66",
            "summarizeBy": "none",
            "annotations": [
              {
                "name": "SummarizationSetBy",
                "value": "Automatic"
              }
            ]
          }
        ],
        "partitions": [
          {
            "name": "PqTablePrivate-d62a028b-42cd-4a3a-b97d-ce883e99a833",
            "mode": "import",
            "source": {
              "type": "m",
              "expression": [
                "let",
                "    Source = PqTable",
                "in",
                "    Source"
              ]
            }
          }
        ],
        "annotations": [
          {
            "name": "PBI_NavigationStepName",
            "value": "Navigation"
          },
          {
            "name": "PBI_ResultType",
            "value": "Table"
          }
        ]
      },
      {
        "name": "PqTablePrivateHidden",
        "isHidden": true,
        "isPrivate": true,
        "lineageTag": "83b80701-e6f4-4c64-8ca1-0fb0c5399c83",
        "columns": [
          {
            "name": "Column1",
            "dataType": "string",
            "sourceColumn": "Column1",
            "lineageTag": "1f665f61-f972-4ac0-ba1c-492b6c98f170",
            "summarizeBy": "none",
            "annotations": [
              {
                "name": "SummarizationSetBy",
                "value": "Automatic"
              }
            ]
          }
        ],
        "partitions": [
          {
            "name": "PqTablePrivateHidden-64c17b17-de9b-402d-8d25-dc37c9285f48",
            "mode": "import",
            "source": {
              "type": "m",
              "expression": [
                "let",
                "    Source = PqTable",
                "in",
                "    Source"
              ]
            }
          }
        ],
        "annotations": [
          {
            "name": "PBI_NavigationStepName",
            "value": "Navigation"
          },
          {
            "name": "PBI_ResultType",
            "value": "Table"
          }
        ]
      },
      {
        "name": "DaxTable",
        "lineageTag": "3e2f348a-a902-4608-b1c7-aa71d407dea0",
        "columns": [
          {
            "type": "calculatedTableColumn",
            "name": "Column1",
            "dataType": "string",
            "isNameInferred": true,
            "isDataTypeInferred": true,
            "sourceColumn": "PqTable[Column1]",
            "lineageTag": "5d04fbcc-0316-42bd-968b-3ba3ef20230e",
            "summarizeBy": "none",
            "annotations": [
              {
                "name": "SummarizationSetBy",
                "value": "Automatic"
              }
            ]
          }
        ],
        "partitions": [
          {
            "name": "DaxTable-e9cdb89e-ccdf-4558-8b65-0d9b4fa93ee9",
            "mode": "import",
            "source": {
              "type": "calculated",
              "expression": "PqTable"
            }
          }
        ],
        "annotations": [
          {
            "name": "PBI_Id",
            "value": "4c2f8b71a4a54f56a62b1456a4812712"
          }
        ]
      },
      {
        "name": "DaxTablePrivate",
        "isPrivate": true,
        "lineageTag": "f3f4992d-0ea8-4f7c-a167-c70760c2f2a5",
        "columns": [
          {
            "type": "calculatedTableColumn",
            "name": "Column1",
            "dataType": "string",
            "isNameInferred": true,
            "isDataTypeInferred": true,
            "sourceColumn": "PqTable[Column1]",
            "lineageTag": "c9b74999-e76b-4621-866d-1a89a62e9c89",
            "summarizeBy": "none",
            "annotations": [
              {
                "name": "SummarizationSetBy",
                "value": "Automatic"
              }
            ]
          }
        ],
        "partitions": [
          {
            "name": "DaxTablePrivate-173e0da7-40bd-4f77-89bd-9a9430bc3733",
            "mode": "import",
            "source": {
              "type": "calculated",
              "expression": "PqTable"
            }
          }
        ],
        "annotations": [
          {
            "name": "PBI_Id",
            "value": "ec0e344532bd46439b6b3cc56d73dd44"
          }
        ]
      },
      {
        "name": "DaxTablePrivateHidden",
        "isHidden": true,
        "isPrivate": true,
        "lineageTag": "9c4a8457-dd9e-4d1e-a89b-2d8d79abf52c",
        "columns": [
          {
            "type": "calculatedTableColumn",
            "name": "Column1",
            "dataType": "string",
            "isNameInferred": true,
            "isDataTypeInferred": true,
            "sourceColumn": "PqTable[Column1]",
            "lineageTag": "b857de53-def6-4096-b0ca-9a44eddfd1fa",
            "summarizeBy": "none",
            "annotations": [
              {
                "name": "SummarizationSetBy",
                "value": "Automatic"
              }
            ]
          }
        ],
        "partitions": [
          {
            "name": "DaxTablePrivateHidden-21fee87e-f8cd-474c-a789-ff9fc8b40e24",
            "mode": "import",
            "source": {
              "type": "calculated",
              "expression": "PqTable"
            }
          }
        ],
        "annotations": [
          {
            "name": "PBI_Id",
            "value": "9ee117f94d894652abee8a5df6f00d77"
          }
        ]
      },
      {
        "name": "PqTableHidden",
        "isHidden": true,
        "lineageTag": "79520b06-87e3-4980-9164-47f2ba6229a8",
        "columns": [
          {
            "name": "Column1",
            "dataType": "string",
            "isHidden": true,
            "sourceColumn": "Column1",
            "lineageTag": "ae0dcab9-8d91-4fdd-98d5-25ee3189b1e5",
            "summarizeBy": "none",
            "annotations": [
              {
                "name": "SummarizationSetBy",
                "value": "Automatic"
              },
              {
                "name": "PBI_ChangedProperties",
                "value": "[\"IsHidden\"]"
              }
            ]
          }
        ],
        "partitions": [
          {
            "name": "PqTableHidden-e11b05cc-a916-4282-99dc-18cfd20b5c0f",
            "mode": "import",
            "source": {
              "type": "m",
              "expression": [
                "let",
                "    Source = PqTable",
                "in",
                "    Source"
              ]
            }
          }
        ],
        "annotations": [
          {
            "name": "PBI_NavigationStepName",
            "value": "Navigation"
          },
          {
            "name": "PBI_ResultType",
            "value": "Table"
          },
          {
            "name": "PBI_ChangedProperties",
            "value": "[\"IsHidden\"]"
          }
        ]
      },
      {
        "name": "DaxTableHidden",
        "isHidden": true,
        "lineageTag": "835545c4-882b-4d4b-b421-95093a38c93e",
        "columns": [
          {
            "type": "calculatedTableColumn",
            "name": "Column1",
            "dataType": "string",
            "isNameInferred": true,
            "isDataTypeInferred": true,
            "isHidden": true,
            "sourceColumn": "PqTable[Column1]",
            "lineageTag": "aa414bfb-83d6-4750-9969-c4754407bd44",
            "summarizeBy": "none",
            "annotations": [
              {
                "name": "SummarizationSetBy",
                "value": "Automatic"
              },
              {
                "name": "PBI_ChangedProperties",
                "value": "[\"IsHidden\"]"
              }
            ]
          }
        ],
        "partitions": [
          {
            "name": "DaxTableHidden-b1232328-42b2-40e6-8c2b-5012d927c408",
            "mode": "import",
            "source": {
              "type": "calculated",
              "expression": "PqTable"
            }
          }
        ],
        "annotations": [
          {
            "name": "PBI_Id",
            "value": "351ba69ae8f6473da85ce525ba0f002c"
          },
          {
            "name": "PBI_ChangedProperties",
            "value": "[\"IsHidden\"]"
          }
        ]
      }
    ],
    "cultures": [
      {
        "name": "en-NZ",
        "linguisticMetadata": {
          "content": {
            "Version": "1.0.0",
            "Language": "en-US",
            "DynamicImprovement": "HighConfidence"
          },
          "contentType": "json"
        }
      }
    ],
    "annotations": [
      {
        "name": "__PBI_TimeIntelligenceEnabled",
        "value": "0"
      },
      {
        "name": "PBIDesktopVersion",
        "value": "2.107.841.0 (22.07)"
      },
      {
        "name": "PBI_QueryOrder",
        "value": "[\"PqTable\",\"PqTablePrivate\",\"PqTablePrivateHidden\",\"PqTableHidden\"]"
      },
      {
        "name": "TabularEditor_SerializeOptions",
        "value": "{\"IgnoreInferredObjects\":true,\"IgnoreInferredProperties\":true,\"IgnoreTimestamps\":true,\"SplitMultilineStrings\":true,\"LocalRelationships\":false,\"LocalPerspectives\":false,\"LocalTranslations\":false,\"PrefixFilenames\":false,\"Levels\":[\"Data Sources\",\"Shared Expressions\",\"Perspectives\",\"Relationships\",\"Roles\",\"Tables\",\"Tables/Columns\",\"Tables/Hierarchies\",\"Tables/Measures\",\"Tables/Partitions\",\"Tables/Calculation Items\",\"Translations\"]}"
      }
    ]
  }
}

      Tables selected:

       

      Tables loaded:

       

      • jeroenterheerdt's avatar
        jeroenterheerdt
        Icon for Microsoft Employee rankMicrosoft Employee
        4 years ago

        thank you, I will pass this along and get back to you!