How to display the dimension attribute as a single line

[amybirc77]

Hi All,

Please see the below screen shots, I have the rows repeated for each value, is there a way to make it appear only once as in the second screen shot? Please advice.

Desired output

Thanks

[amybirc77]

@v-jianboli-msft thanks a lot

[v-jianboli-msft · Accepted solution]

Hi @amybirc77 ,

Please try:

First add an index column:

Then add three columns like this:

Remove the original columns and index column

Rename columns

Here is the M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jY8xCsMwDEWvYrw2NpIVqXFn7zlAyOAhQyGYQqbevlLSPQZLGEuP970svtR9r4cfPGBw82drei1ba297Y2LtCEI5crYlCA7Ylfo9/Drc0E8tInyBHrAdCQ6hE04mZsNQp9jrpCt1zGdwFaZeIZ4/NbMOU69vtJwkU0TjkwqpV0hiBIwSJ2MJH39w/QE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Enterprise Name" = _t, #"RMS Status Description" = _t, #"Entity Name" = _t, #"Shared Master Generated Cube v2 RMS Inventory Open Count" = _t, #"RMS Inventory Balance Amount" = _t, CreateDate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Enterprise Name", type text}, {"RMS Status Description", type text}, {"Entity Name", type text}, {"Shared Master Generated Cube v2 RMS Inventory Open Count", Int64.Type}, {"RMS Inventory Balance Amount", type text}, {"CreateDate", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Enterprise Name'", each if [Index] = 1 then [Enterprise Name] else ""),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "RMS Status Description'", each if [Index]=1 then [RMS Status Description] else ""),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Entity Name'", each if [Index] = 1 then [Entity Name] else ""),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Enterprise Name", "RMS Status Description", "Entity Name", "Index"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Enterprise Name'", "RMS Status Description'", "Entity Name'", "Shared Master Generated Cube v2 RMS Inventory Open Count", "RMS Inventory Balance Amount", "CreateDate"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Enterprise Name'", "Enterprise Name"}, {"RMS Status Description'", "RMS Status Description"}, {"Entity Name'", "Entity Name"}})
in
    #"Renamed Columns"

Final output: