Columns Data Reorganization

[Lily36876]

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 to reorganizing like follow example,

please help some idea thanks!!

 

[Jihwan_Kim · Accepted solution]

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

[Lily36876]

Thanks for reply!! Is really useful for me

[Shaurya]

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