Creating dimensional tables from master direct query

[swolfe2]

I have a business requirement where the report is looking at a Snowflake view which has over a billion records. In order to create a better star schema, I've identified fields that I need to create imported versions of and then join back to the model. That way, filters will load much faster since they're looking at such a smaller fraction of records than querying back to the main Snowflake view to get values.

I've attempted to write M that would let me plug in the field I'm looking for, and then it would create the dimensional table. However, when it gets to the step to remove null values, it hangs and never actually returns. If I don't use a variable, and use the actual field name in the brackets, it works as expected.

Here's the version that works:

let
    //Source is the main Snowflake view, so will always be updated
    Source = V_SNOWFLAKE,

    //Remove everything but FIELD_NAME
    #"Removed Other Columns" = Table.SelectColumns(Source,{"FIELD_NAME"}),

    //Remove duplicate entries
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),

    //Remove null values from table
    #"Filtered Rows" = Table.SelectRows(#"Removed Duplicates", each ([FIELD_NAME] <> null)),

    //Sort alpha to improve readability
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"FIELD_NAME", Order.Ascending}})
in
    #"Sorted Rows"

Here's the version that doesn't work:

let
    //Set field name variable
    varFieldName = "SNOWFLAKE_FIELD_NAME",

    //Source is the main Snowflake view, so will always be updated
    Source = V_SNOWFLAKE,

    //Remove everything but varFieldName
    #"Removed Other Columns" = Table.SelectColumns(Source,{varFieldName}),

    //Remove duplicate entries
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),

    //Remove null values from table
    #"Filtered Rows" = Table.SelectRows(#"Removed Duplicates", each ([varFieldName] <> null)),

    //Sort alpha to improve readability
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{varFieldName, Order.Ascending}})
in
    #"Sorted Rows"

Any help/input you all could provide would be grealty appreciated!

[v-yingjl]

Hi @swolfe2 ,

You can use Record.Field() in the Filter Rows query to get the column name by variable and filter it, here is the sample query:

let
    varFieldName = "FIELD_NAME",
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WilTSUTJUitWJVnICsozALGcgyxjMcgGyTMAsIMMUzHAFssxgQuYwhgWYEQVkWSrFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FIELD_NAME = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"FIELD_NAME", type text}, {"Value", Int64.Type}}),
    #"Remove Other Columns" = Table.SelectColumns(#"Changed Type",{varFieldName}),
    #"Remove Duplicates" = Table.Distinct(#"Remove Other Columns"),
    #"Filtered Rows" = Table.SelectRows(#"Remove Duplicates",each Record.Field(_,varFieldName) <> ""),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{varFieldName, Order.Ascending}})
in
    #"Sorted Rows"

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[edhans]

How does Record.Field fold with a Direct Query model?

[edhans]

Get rid of the sort. You can sort in Power BI in the visuals. 

Honestly, you should have views created and connect to those straight away. Creating a star schema from flat is painful enough with an import model. Doing it with a DQ model will be super slow, especially with 1 billion records. 

If the field name has a special character in it, like +, -, etc. then the field has to be referenced as [#"Field-Name"] in the direct M code. Assigning it to a variable will circumvent that.

But really, get views. 😁