The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hello , please I need help to do this ..I need to convert this table
Date | Value |
2021-08-09 | 10 |
2021-08-09 | 20 |
2021-08-09 | 30 |
2021-08-10 | 50 |
to be in this format
30 | 20 | 10 | 2021-08-09 |
50 | 2021-08-10 |
How I can do this ?
Solved! Go to Solution.
Hi @ayaasfour
There is a simple method that can be implemented directly in the Matrix, but the result display will be a little different, you can refer to it .
Put the value column in Columns and Values at the same time in Visual Format .
Best Regards
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
This M code works
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrDUNbDQNTIwMlTSUTI0UIrVQRMzwiJmDBEzNEASMwWKxQIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Value", Int64.Type}}),
Partition = Table.Group(#"Changed Type", {"Date"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
#"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Value", "Index"}, {"Value", "Index"}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Partition", {{"Index", type text}}, "en-IN"), List.Distinct(Table.TransformColumnTypes(#"Expanded Partition", {{"Index", type text}}, "en-IN")[Index]), "Index", "Value")
in
#"Pivoted Column"
Hope this helps.
Hi,
This M code works
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrDUNbDQNTIwMlTSUTI0UIrVQRMzwiJmDBEzNEASMwWKxQIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Value", Int64.Type}}),
Partition = Table.Group(#"Changed Type", {"Date"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
#"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Value", "Index"}, {"Value", "Index"}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Partition", {{"Index", type text}}, "en-IN"), List.Distinct(Table.TransformColumnTypes(#"Expanded Partition", {{"Index", type text}}, "en-IN")[Index]), "Index", "Value")
in
#"Pivoted Column"
Hope this helps.
Hi @ayaasfour
There is a simple method that can be implemented directly in the Matrix, but the result display will be a little different, you can refer to it .
Put the value column in Columns and Values at the same time in Visual Format .
Best Regards
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Can not be solved using matrix?
@ayaasfour , not excat same display
create a rank column in table
rank = rankx(filter(Table, [Date] = earlier([Date]) ),[Value])
Create a matrix with Rank as the column date as row, value on values
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.
User | Count |
---|---|
138 | |
104 | |
75 | |
60 | |
60 |
User | Count |
---|---|
263 | |
128 | |
115 | |
95 | |
78 |