Sunday, November 19, 2023

How to view the Schema of a Table or Dataset in Power BI

How to view the Schema of a Table or Dataset in Power Query
Scenario:
Suppose we have a sample table/dataset "tbl_Sample" in Power BI as follows:

Now we can view the Schema ( Columns definition) of a Table or Dataset using M-Query is as follows:
= Table.SelectColumns(Table.Schema(ChangeType), {"Name", "Position", "TypeName", "Kind", "IsNullable"})

Result:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldFRa4MwEAfw7+KzoLkk6h63sb2VllL6Ij5kXZCAS0rM9vl7sb1MN0Ynwkngl+Pyv7bNZFEXUEKZ5RkDjnWv3o3D/8ugT8E7a04jnmTW5RE3/8H8hllJGrjAetCD/jKjcfbXFbH+CtAVlmYSEutG2T7O9Oo/TYiQlSThjqwI8jRFHeGTsvjNJeXBRJJTy63Hnnqc05qo/KaA9fF8HvQcNgSrOzCFlbbBRYV154IK8UFH3eug3ga9eNQDaSGjflbeu/BTN6t6p2ghLUHAlIO9rmyhKTRIexBTvAf38Ufv7gI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order Date" = _t, #"Prod ID" = _t, #"Prod Name" = _t, #"Prod Category" = _t, #"Order Qnty" = _t]),

ChangeType = Table.TransformColumnTypes(Source,{{"Order Date", type date}, {"Prod ID", Int64.Type}, {"Prod Name", type text}, {"Prod Category", type text}, {"Order Qnty", Int64.Type}}),

ViewSchema = Table.SelectColumns(Table.Schema(ChangeType), {"Name", "Position", "TypeName", "Kind", "IsNullable"})
in
    ViewSchema

I have selected only the few columns from the Schema Definition :

-----------------------------------------------------------------------------------

Suppose, if we want generate a Native SQL Select Statement from the Columns of a Dataset in Power Query, we can use the following M-Query:

vColumnList = List.Transform(Table.ColumnNames( ChangeType), each "[" & _ & "]"),
vSelectQuery
Text.Replace(Text.Combine({"Select"} & vColumnList,","), "t,", "t ") & " From TableName"

Result:
In the following result, we can replace the [TableName] with the Original Table name to use this Native SQL Query to run :


 
Thanks, TAMATAM

1 comment:

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog