distributing a column to rows

[asifrasool]

Hello Everyone,

I'm in to a very strange situation right now.

this is my sample data

Following table is actuall table

 

I want above table to be converted like this

It is sure that the medication columns will be only 3.

Thank you so much.

[Smalfly]

Hi @asifrasool ,

you can easily do that in Power Query by using pivot columns functionality.

Please check the MS documentation on it.

[Greg_Deckler · Accepted solution]

@asifrasool Try this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLUN9Q3MjAwADJTKxJzC3JSFQyVYnVwShqBJY3AkkboksZgSWPskib4JE3xSZqBJU3AksbokuZIkibokhZIxpqiS1oqxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Collum A" = _t, Date = _t, Medication = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Collum A", type text}, {"Date", type text}, {"Medication", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Collum A", "Date"}, {{"Medications", each _, type table [Collum A=nullable number, Date=nullable date, Medication=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Medication 1", each List.First(Table.ToList(Table.SelectColumns([Medications],"Medication")))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Medication 2", each if Table.RowCount([Medications]) = 2 then List.Last(Table.ToList(Table.SelectColumns([Medications],"Medication"))) else if Table.RowCount([Medications]) = 3 then List.Last(List.RemoveLastN(Table.ToList(Table.SelectColumns([Medications],"Medication")),1)) else null),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Medication 3", each if Table.RowCount([Medications]) = 3 then List.Last(Table.ToList(Table.SelectColumns([Medications],"Medication"))) else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Medications"})
in
    #"Removed Columns"