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.
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.
Solved! Go to 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"
Hi @asifrasool ,
you can easily do that in Power Query by using pivot columns functionality.
Please check the MS documentation on it.
@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"
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |