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
This information is very good thanks for giving
ReplyDelete