Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a Table of Tables:
Custom |
Table |
Table |
Inside each Table, it has the following Format:
Table at index 0:
ID | Col1 | Col2 |
123 | sdds | rgfg |
234 | lsfg | ldfg |
Table at index 1:
ID | Col1 | Col2 |
123 | ksd | woed |
234 | rod | mkd |
I want to add a custom column to each table such that,
1st table at index 0 will have the new column values as "AAA" and
2nd table at index 1 will have the new column values as "BBB". So resultant tables will look like this:
Custom |
Table |
Table |
Table at index 0:
ID | Col1 | Col2 | Metric |
123 | sdds | rgfg | AAA |
234 | lsfg | ldfg | AAA |
Table at index 1:
ID | Col1 | Col2 | Metric |
123 | ksd | woed | BBB |
234 | rod | mkd | BBB |
Is this possible with pure, concise M-code?
Solved! Go to Solution.
Hello @Anonymous
try this solution.
Creates an Index with AAA, BBB to ZZZ, adds a Index-Column to your table an transforms it to your index logic. Then this new index-column in integrated in your tables with a Table.ReplaceValue
let
TableA = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRKk5JKQZSRelp6UqxOtFKRsYmQG5OMZALpFJAorEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Col1 = _t, Col2 = _t]),
TableB = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRKk5JKQZSRelp6UqxOtFKRsYmQG5OMZALpFJAorEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Col1 = _t, Col2 = _t]),
TableList = {TableA, TableB},
#"Converted to Table" = Table.FromList(TableList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
CreateIndex = List.Transform({"A".."Z"}, each Text.Combine(List.Repeat({_}, 3))),
#"Added Index" = Table.AddIndexColumn(#"Converted to Table", "Index", 0, 1),
ConvertIndex = Table.TransformColumns
(
#"Added Index",
{
{
"Index",
each CreateIndex{_}
}
}
),
AddColumnToTable = Table.ReplaceValue
(
ConvertIndex,
each [Column1],
(tbl)=> Table.AddColumn(tbl[Column1], "Metric", each tbl[Index]),
Replacer.ReplaceValue,
{"Column1"}
),
#"Removed Other Columns" = Table.SelectColumns(AddColumnToTable,{"Column1"}),
Column1 = #"Removed Other Columns"[Column1]
in
Column1
Copy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
What i meant is the text in the Metric column could be anything for each of the tables. Later i want to combine and Pivot the tables on this `Metric` column. Can it not be something shorter like what List.Positions gives?
e.g. List.Positions([Custom]) i.e. a 0 for 1st table & a 1 for 2nd table.
I can't add an Index column, because then each table rows will have the same set of index numbers.
Custom | Metric |
Table | 0 |
Table | 1 |
So if i add another Conditional column based on Metric column, then i could do something like:
Table.AddColumn([Metric], each if [Metric]=0 then "UNIQ" else "ND" )
Custom | Metric |
Table | UNIQ |
Table | ND |
Is there a shorter code to do something like this? I am trying to reduce the number of lines of code inorder for the query to execute quickly (optimize it).
Hello @Anonymous
somehow you have to create an Index... automatically or manually, then add a Index to your table-column and then somehow add a new column to every single table, using you index-table. If you are satisfied with a metric of 1, 2 etc. then you can use a shorter code like this
let
TableA = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRKk5JKQZSRelp6UqxOtFKRsYmQG5OMZALpFJAorEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Col1 = _t, Col2 = _t]),
TableB = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRKk5JKQZSRelp6UqxOtFKRsYmQG5OMZALpFJAorEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Col1 = _t, Col2 = _t]),
TableList = {TableA, TableB},
#"Converted to Table" = Table.FromList(TableList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Added Index" = Table.AddIndexColumn(#"Converted to Table", "Index", 1, 1),
AddColumnToTable = Table.ReplaceValue
(
#"Added Index",
each [Column1],
(tbl)=> Table.AddColumn(tbl[Column1], "Metric", each tbl[Index]),
Replacer.ReplaceValue,
{"Column1"}
),
#"Removed Other Columns" = Table.SelectColumns(AddColumnToTable,{"Column1"}),
Column1 = #"Removed Other Columns"[Column1]
in
Column1
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
@Jimmy801 i revisited your earlier code and actually you have given room for improvement. 🙂
I clubbed the steps into 2 steps like this :
let
TableA = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRKk5JKQZSRelp6UqxOtFKRsYmQG5OMZALpFJAorEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Col1 = _t, Col2 = _t]),
TableB = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRKk5JKQZSRelp6UqxOtFKRsYmQG5OMZALpFJAorEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Col1 = _t, Col2 = _t]),
TableList = {TableA, TableB},
ConvertedToTable = Table.FromList(TableList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
ConvertIndex = Table.TransformColumns( Table.AddIndexColumn(ConvertedToTable, "Index", 0, 1), { {"Index", each {"UNIQ","ND"}{_} } } ),
AddColumnToTable = Table.Combine( Table.SelectColumns( Table.ReplaceValue ( ConvertIndex, each [Column1], (tbl) => Table.AddColumn(tbl[Column1], "Metric", each tbl[Index]), Replacer.ReplaceValue, {"Column1"} ), {"Column1"})[Column1] )
in
AddColumnToTable
I especially liked this step where you are adding index, then transforming and later replacing the index values with textual ones inside each table.
= Table.TransformColumns( Table.AddIndexColumn(ConvertedToTable, "Index", 0, 1), { {"Index", each {"UNIQ","ND"}{_} } } )
Hello @Anonymous
try this solution.
Creates an Index with AAA, BBB to ZZZ, adds a Index-Column to your table an transforms it to your index logic. Then this new index-column in integrated in your tables with a Table.ReplaceValue
let
TableA = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRKk5JKQZSRelp6UqxOtFKRsYmQG5OMZALpFJAorEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Col1 = _t, Col2 = _t]),
TableB = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRKk5JKQZSRelp6UqxOtFKRsYmQG5OMZALpFJAorEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Col1 = _t, Col2 = _t]),
TableList = {TableA, TableB},
#"Converted to Table" = Table.FromList(TableList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
CreateIndex = List.Transform({"A".."Z"}, each Text.Combine(List.Repeat({_}, 3))),
#"Added Index" = Table.AddIndexColumn(#"Converted to Table", "Index", 0, 1),
ConvertIndex = Table.TransformColumns
(
#"Added Index",
{
{
"Index",
each CreateIndex{_}
}
}
),
AddColumnToTable = Table.ReplaceValue
(
ConvertIndex,
each [Column1],
(tbl)=> Table.AddColumn(tbl[Column1], "Metric", each tbl[Index]),
Replacer.ReplaceValue,
{"Column1"}
),
#"Removed Other Columns" = Table.SelectColumns(AddColumnToTable,{"Column1"}),
Column1 = #"Removed Other Columns"[Column1]
in
Column1
Copy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
101 | |
49 | |
19 | |
12 | |
11 |