Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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:
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:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |