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.
I need cell values in yellow to be replaced by values in cells in green. Can anyone help? Thank you
Solved! Go to Solution.
Hi @Augusto_101 ,
Go to the Transform tab and click on Transpose. This will transpose your table, swapping rows and columns. --> Add a index column --> Replace Values --> Remove the index column --> Transpose.
All steps and the output:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRMtQ30jcyMDIAMoHICMIzhPCMYbxYHbBaX38/F8dIiFxIqGswnBPu6uIH4YJU+helpBaBlOfn6bokVhZDmX6Z6RklIE5IaWoxTALMhsuEp6ZAJEDGGJoYmxuaQ52CQAgpE9xS5kCuhR6CNNKzhLPNlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t, Column12 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}}),
Transpose = Table.Transpose(#"Changed Type"),
#"Added Index" = Table.AddIndexColumn(Transpose, "Index", 1, 1, Int64.Type),
ReplaceValues = Table.ReplaceValue(#"Added Index", each [Column3], each if List.Contains({2,4,6},[Index]) then [Column1] else [Column3], Replacer.ReplaceValue,{"Column3"}),
#"Removed Columns" = Table.RemoveColumns(ReplaceValues,{"Index"}),
TransposeBack = Table.Transpose(#"Removed Columns")
in
TransposeBack
Best Regards,
Gao
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
@Augusto_101 , add more column names if necessary
let
Source = your_table,
col_names = {"Column8", "Column10", "Column12"},
z =
Table.ReplaceRows(
Source, 2, 1,
{Record.RemoveFields(Source{2}, col_names) & Record.SelectFields(Source{0}, col_names)}
)
in
z
Hi @Augusto_101 ,
Go to the Transform tab and click on Transpose. This will transpose your table, swapping rows and columns. --> Add a index column --> Replace Values --> Remove the index column --> Transpose.
All steps and the output:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRMtQ30jcyMDIAMoHICMIzhPCMYbxYHbBaX38/F8dIiFxIqGswnBPu6uIH4YJU+helpBaBlOfn6bokVhZDmX6Z6RklIE5IaWoxTALMhsuEp6ZAJEDGGJoYmxuaQ52CQAgpE9xS5kCuhR6CNNKzhLPNlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t, Column12 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}}),
Transpose = Table.Transpose(#"Changed Type"),
#"Added Index" = Table.AddIndexColumn(Transpose, "Index", 1, 1, Int64.Type),
ReplaceValues = Table.ReplaceValue(#"Added Index", each [Column3], each if List.Contains({2,4,6},[Index]) then [Column1] else [Column3], Replacer.ReplaceValue,{"Column3"}),
#"Removed Columns" = Table.RemoveColumns(ReplaceValues,{"Index"}),
TransposeBack = Table.Transpose(#"Removed Columns")
in
TransposeBack
Best Regards,
Gao
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
Hi @Augusto_101
1. Remove first two rows
2. Use first rows as Header
3. Double click on header & Rename header as you want
If solve your question, please hit the 👍 and mark as SOLUTION
Hi Pijush. Thank you for your reply but unfortunately that won't help because I need the date to be in the table so I can filter it in my report later on.
Hi @Augusto_101
You can do what @PijushRoy has proposed and, once you have removed two first rows, then unfold the option of use first row as headers to select the other one: "Use headers as first row"
Then you will get something like:
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.