Forum Discussion
Solved
Columns Data Reorganization
Hi Guys, I want to brief product name , all data comes from sql server not csv. first three columns data is existing in same table , but product name is too long to show on dashboard, so I want t...
Hi,
Please check the below picture and the attached pbix file.
It is for creating a new column.
Ideal Type CC = VAR _newproduct = REPLACE ( Data[Product], 1, LEN ( Data[Brand] ), "" ) RETURN Data[Short Name] & _newproduct
Hi Lily36876,
Split the text by first occurence of space, change the first part to Upper Case, extract first two characters from it and merge the result with the second part of original string.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsnML1JwTEnJTC5R8MksUHDPyS9Xio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Name", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Name.1", "Name.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Name.1", type text}, {"Name.2", type text}}),
#"Uppercased Text" = Table.TransformColumns(#"Changed Type1",{{"Name.1", Text.Upper, type text}}),
#"Added Custom" = Table.AddColumn(#"Uppercased Text", "Custom", each Text.Range([Name.1],0,2)),
#"Merged Columns" = Table.CombineColumns(#"Added Custom",{"Custom", "Name.2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
#"Removed Columns" = Table.RemoveColumns(#"Merged Columns",{"Name.1"})
in
#"Removed Columns"
Works for you? Mark this post as a solution if it does!
Consider taking a look at my blog: Forecast Period - Previous Forecasts