Forum Discussion

Lily36876's avatar
Lily36876
Helper II
3 years ago
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 to reorganizing like follow example,

please help some idea thanks!!

 

 

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    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

3 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    3 years ago

    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
  • Shaurya's avatar
    Shaurya
    Memorable Member
    3 years ago

    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