Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
amybirc77
Helper I
Helper I

How to display the dimension attribute as a single line

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.

amybirc77_0-1680011753035.png

Desired output

amybirc77_1-1680011782390.png

Thanks

1 ACCEPTED SOLUTION
v-jianboli-msft
Community Support
Community Support

Hi @amybirc77 ,

 

Please try:

First add an index column:

vjianbolimsft_2-1680142978821.png

 

vjianbolimsft_0-1680142946630.png

 

Then add three columns like this:

vjianbolimsft_3-1680143013973.png

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:

vjianbolimsft_4-1680143217722.png

 

 

View solution in original post

2 REPLIES 2
amybirc77
Helper I
Helper I

@v-jianboli-msft thanks a lot

v-jianboli-msft
Community Support
Community Support

Hi @amybirc77 ,

 

Please try:

First add an index column:

vjianbolimsft_2-1680142978821.png

 

vjianbolimsft_0-1680142946630.png

 

Then add three columns like this:

vjianbolimsft_3-1680143013973.png

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:

vjianbolimsft_4-1680143217722.png

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.