Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Is it possible to generate dynamically for all columns of a given table the following
[id=nullable Int64.Type, year=nullable Int64.Type, amount=nullable number, ing_est=nullable datetime]
sample query
let
src=Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZBBCsMwDAT/4nMiVivLtvSO3EL+/4267aUlhWJdBIJhR3ueZUSUrRD0uXaFjjCprfX3te6IHfVAJDxJ0WaYU67tg60v1jkqhCugzYXlJOuNTuntm1RPjGQVRL+Tr//oGOai3WyF/aGpI9GTsytiRdOPmaNMhlhd1PzH3jT9UOQslE08FjVnzpN0ib6seWevBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, year = _t, amount = _t, ing_est = _t]),
ct = Table.TransformColumnTypes(src,{{"id", Int64.Type}, {"year", Int64.Type}, {"amount", type number}, {"ing_est", type datetime}}),
#"Grouped Rows" = Table.Group(ct, {"id"}, {{"ad", each _, type table [id=nullable Int64.Type, year=nullable Int64.Type, amount=nullable number, ing_est=nullable datetime]}})
in
#"Grouped Rows"
Solved! Go to Solution.
Now I probably see what you want to achive. You want to replace this part of Table.Group. Is that correct?
If yes, you can just delete that part of Table.Group and use Table.Combine to preserve column types, like this:
let
src=Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZBBCsMwDAT/4nMiVivLtvSO3EL+/4267aUlhWJdBIJhR3ueZUSUrRD0uXaFjjCprfX3te6IHfVAJDxJ0WaYU67tg60v1jkqhCugzYXlJOuNTuntm1RPjGQVRL+Tr//oGOai3WyF/aGpI9GTsytiRdOPmaNMhlhd1PzH3jT9UOQslE08FjVnzpN0ib6seWevBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, year = _t, amount = _t, ing_est = _t]),
ct = Table.TransformColumnTypes(src,{{"id", Int64.Type}, {"year", Int64.Type}, {"amount", type number}, {"ing_est", type datetime}}, "en-US"),
GroupedRows = Table.Group(ct, {"id"}, {{"ad", each _, type table}}),
Combined = Table.Combine(GroupedRows[ad])
in
Combined
Hi @smpa01, check this:
Output:
let
src=Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZBBCsMwDAT/4nMiVivLtvSO3EL+/4267aUlhWJdBIJhR3ueZUSUrRD0uXaFjjCprfX3te6IHfVAJDxJ0WaYU67tg60v1jkqhCugzYXlJOuNTuntm1RPjGQVRL+Tr//oGOai3WyF/aGpI9GTsytiRdOPmaNMhlhd1PzH3jT9UOQslE08FjVnzpN0ib6seWevBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, year = _t, amount = _t, ing_est = _t]),
ct = Table.TransformColumnTypes(src,{{"id", Int64.Type}, {"year", Int64.Type}, {"amount", type number}, {"ing_est", type datetime}}, "en-US"),
GroupedRows = Table.Group(ct, {"id"}, {{"ad", each _, type table}, {"Schema", each Record.FromTable(Table.RenameColumns(Table.Schema(_)[[Name], [TypeName]], {{"TypeName", "Value"}}, MissingField.Ignore)), type record}})
in
GroupedRows
@dufoq3 this is good but I want to dynamically pass on that bit to finally give me the following
//didnot work
let
src=Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZBBCsMwDAT/4nMiVivLtvSO3EL+/4267aUlhWJdBIJhR3ueZUSUrRD0uXaFjjCprfX3te6IHfVAJDxJ0WaYU67tg60v1jkqhCugzYXlJOuNTuntm1RPjGQVRL+Tr//oGOai3WyF/aGpI9GTsytiRdOPmaNMhlhd1PzH3jT9UOQslE08FjVnzpN0ib6seWevBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, year = _t, amount = _t, ing_est = _t]),
ct = Table.TransformColumnTypes(src,{{"id", Int64.Type}, {"year", Int64.Type}, {"amount", type number}, {"ing_est", type datetime}}, "en-US"),
GroupedRows = let x = Table.Group(ct, {"id"}, {{"ad", each _, type table}, {"Schema", each Record.FromTable(Table.RenameColumns(Table.Schema(_)[[Name], [TypeName]], {{"TypeName", "Value"}}, MissingField.Ignore)), type record}})[Schema]{0} in Table.Group(ct, {"id"}, {{"ad", each _, type table x}})
in
GroupedRows
What is the purpose of this? Do you want to replase table types with another table types or what? If yes, there is more efficient way:
let
Source = #table(type table[Name=text, Age=Int64.Type, Date of Birth=date], {{"Peter", 20, #date(2000,10,15)}, {"Martin", 25, #date(2003,12,10)}}),
ChangedType = Table.TransformColumnTypes(Source,{{"Age", type number}, {"Date of Birth", type datetime}}),
RestoredTypes = Value.ReplaceType(ChangedType, Value.Type(Source))
in
RestoredTypes
The purpose is to create a function that can group any table with the dynamically generated schema of that table to be passed on to Table.Group.
Now I probably see what you want to achive. You want to replace this part of Table.Group. Is that correct?
If yes, you can just delete that part of Table.Group and use Table.Combine to preserve column types, like this:
let
src=Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZBBCsMwDAT/4nMiVivLtvSO3EL+/4267aUlhWJdBIJhR3ueZUSUrRD0uXaFjjCprfX3te6IHfVAJDxJ0WaYU67tg60v1jkqhCugzYXlJOuNTuntm1RPjGQVRL+Tr//oGOai3WyF/aGpI9GTsytiRdOPmaNMhlhd1PzH3jT9UOQslE08FjVnzpN0ib6seWevBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, year = _t, amount = _t, ing_est = _t]),
ct = Table.TransformColumnTypes(src,{{"id", Int64.Type}, {"year", Int64.Type}, {"amount", type number}, {"ing_est", type datetime}}, "en-US"),
GroupedRows = Table.Group(ct, {"id"}, {{"ad", each _, type table}}),
Combined = Table.Combine(GroupedRows[ad])
in
Combined
Smashing !!! Any performance implication you may think of for a large table?
No, there shouldn't be any. Another advantage of Table.Combine is that it will expand each column of every table dynamicaly, besides if you Expand Columns via button - it will hardcode column names (but only for first table if I'm correct).
this is the end result I want to achieve once the schema is dynamically generated and passed on to Table.Group
The challenge is to dynamically somehow replace the following portion
[id=nullable Int64.Type, year=nullable Int64.Type, amount=nullable number, ing_est=nullable datetime]
with a function call in the code below
let
src=Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZBBCsMwDAT/4nMiVivLtvSO3EL+/4267aUlhWJdBIJhR3ueZUSUrRD0uXaFjjCprfX3te6IHfVAJDxJ0WaYU67tg60v1jkqhCugzYXlJOuNTuntm1RPjGQVRL+Tr//oGOai3WyF/aGpI9GTsytiRdOPmaNMhlhd1PzH3jT9UOQslE08FjVnzpN0ib6seWevBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, year = _t, amount = _t, ing_est = _t]),
ct = Table.TransformColumnTypes(src,{{"id", Int64.Type}, {"year", Int64.Type}, {"amount", type number}, {"ing_est", type datetime}}),
#"Grouped Rows" = Table.Group(ct, {"id"}, {{"ad", each _, type table [id=nullable Int64.Type, year=nullable Int64.Type, amount=nullable number, ing_est=nullable datetime]}}),
#"Expanded ad" = Table.ExpandTableColumn(#"Grouped Rows", "ad", {"id", "year", "amount", "ing_est"}, {"id.1", "year", "amount", "ing_est"})
in
#"Expanded ad"
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
63 | |
61 | |
23 | |
18 | |
12 |