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.
Morning all,
I've never done this before, and it is just confusing. I have two columns, One for the Operating System and one for the version.
What I need to do is Trim bits from both columns and make one combined column, as such...
Column 1 Column 2 Column 3
Windows 10 Enterprise 10.0 (16299) Windows 10 (16299)
Windows 7 Professional 6.1 (7601) Windows 7 (7601)
Mac OS X 10.13.6 Mac OS X (10.13.6)
Windows 8.1 Enterprise 6.3 (9600) Windows 8.1 (9600)
I would be grateful if anyone could point me in the right direction.
Paul
Solved! Go to Solution.
Hi @Noblelox,
Follow these steps:
// Table
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs/MS8kvL1YwNFBwzStJLSooyixOVdJRMjTQM1DQMDQzsrTUVIrVQSg0Vwgoyk9LLS7OzM9LzAGqNNMzVNAwNzMwhKjzTUxW8A9WiICYYWisZ4ai3QKoGsUiMz1jBQ1LMwMDoPZYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Column 1" = _t, #"Column 2" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column 1", type text}, {"Column 2", type text}}),
#"Replaced Value1" = Table.ReplaceValue(#"Changed Type","Enterprise","",Replacer.ReplaceText,{"Column 1"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","Professional","",Replacer.ReplaceText,{"Column 1"}),
#"Added Prefix" = Table.TransformColumns(#"Replaced Value2", {{"Column 2", each "(" & _, type text}}),
#"Added Suffix" = Table.TransformColumns(#"Added Prefix", {{"Column 2", each _ & ")", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Added Suffix","))",")",Replacer.ReplaceText,{"Column 2"}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Replaced Value", "Column 2", Splitter.SplitTextByEachDelimiter({"("}, QuoteStyle.Csv, true), {"Column 2.1", "Column 2.2"}),
#"Added Prefix1" = Table.TransformColumns(#"Split Column by Delimiter1", {{"Column 2.2", each "(" & _, type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Added Prefix1",{"Column 2.1"}),
#"Inserted Merged Column" = Table.AddColumn(#"Removed Columns", "Column 3", each Text.Combine({[Column 1], [Column 2.2]}, ""), type text),
#"Renamed Columns" = Table.RenameColumns(#"Inserted Merged Column",{{"Column 2.2", "Column 2"}})
in
#"Renamed Columns"
Figure: Start
Figure: End
Greetings FrankAT
Hi @Noblelox,
Follow these steps:
// Table
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs/MS8kvL1YwNFBwzStJLSooyixOVdJRMjTQM1DQMDQzsrTUVIrVQSg0Vwgoyk9LLS7OzM9LzAGqNNMzVNAwNzMwhKjzTUxW8A9WiICYYWisZ4ai3QKoGsUiMz1jBQ1LMwMDoPZYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Column 1" = _t, #"Column 2" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column 1", type text}, {"Column 2", type text}}),
#"Replaced Value1" = Table.ReplaceValue(#"Changed Type","Enterprise","",Replacer.ReplaceText,{"Column 1"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","Professional","",Replacer.ReplaceText,{"Column 1"}),
#"Added Prefix" = Table.TransformColumns(#"Replaced Value2", {{"Column 2", each "(" & _, type text}}),
#"Added Suffix" = Table.TransformColumns(#"Added Prefix", {{"Column 2", each _ & ")", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Added Suffix","))",")",Replacer.ReplaceText,{"Column 2"}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Replaced Value", "Column 2", Splitter.SplitTextByEachDelimiter({"("}, QuoteStyle.Csv, true), {"Column 2.1", "Column 2.2"}),
#"Added Prefix1" = Table.TransformColumns(#"Split Column by Delimiter1", {{"Column 2.2", each "(" & _, type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Added Prefix1",{"Column 2.1"}),
#"Inserted Merged Column" = Table.AddColumn(#"Removed Columns", "Column 3", each Text.Combine({[Column 1], [Column 2.2]}, ""), type text),
#"Renamed Columns" = Table.RenameColumns(#"Inserted Merged Column",{{"Column 2.2", "Column 2"}})
in
#"Renamed Columns"
Figure: Start
Figure: End
Greetings FrankAT
Spot on FrankAT!
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 |
---|---|
107 | |
100 | |
80 | |
63 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |