Forum Discussion
laciodrom_80
9 years agoHelper IV
Create a calculated table
Hi guys, I've got a table like this one: ID COLUMN 1 22;25 2 10;5 3 100;70 from which I'd like to create this new one: ID COLUMN 1 22 1 25 ...
- 9 years ago
Yes, this worked the way I expected. I used a slicer for ITEM and a table for the Names table and it worked like a champ. Here are the queries I used.
Items table
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfIsSc0F0UZG1kamSrE60UpGUFEQbWhgDRE0hgoagwUNlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, ITEMS = _t, TABLE2_IDS = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"ITEMS", type text}, {"TABLE2_IDS", type text}}), #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type","TABLE2_IDS",Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv),{"TABLE2_IDS.1", "TABLE2_IDS.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"TABLE2_IDS.1", Int64.Type}, {"TABLE2_IDS.2", Int64.Type}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"ID", "ITEMS"}, "Attribute", "Value"), #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Value", "TABLE2_IDS"}}) in #"Renamed Columns"Names table
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlXSUfJLzE01VIrViVYyNIByjcBcIyMo1xjChSk2gSqGqTZVio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, NAMES = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}})
in
#"Changed Type"
Greg_Deckler
9 years agoCommunity Champion
I would suggest that you do that in Power Query by just splitting the column on ";" and then unpivoting the resulting columns.