Convert multiple rows same date into on rows and convert other rows into columns

[ayaasfour]

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 ?

[v-yetao1-msft · Accepted 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.

[Ashish_Mathur · Accepted solution]

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.

[v-yetao1-msft · Accepted 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.

[ayaasfour]

Can not be solved using matrix?

[amitchandak]

@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