Forum Discussion
How to Create data table from row data in power query
Hello Sir, I wish to create table like below table in power query with same below results. Also please note i have not added week of the month in smaple data sheet b'coz i want to learn to create...
Hi Anonymous ,
You could use the function of List.Max and List.Min. Try the code below.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VZDBDcMwDAN3ybtAZUmW3VmK7r9GQ7ZhoA8N+GJRuff78Od4uo19PI5hdqbbeXweIH4R/xEXCb2ZJPkneU87v0aWyD0tJ3KLaJqvOjNMJC8StpDXBvvuiWCmiHqiXkwR9ST/J7aIejLGj19kipQjbwfcAA35CmR2byDTky+7N5IJB7lFUmTDwbRuFKTG4sxulCSx4cxulGRxZnWjuFvD2CYyRRIOyrprkgUH5d01yLbgHt01ScBBVXdNUnBQ3ODzBQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Office = _t, Workstatics = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Office", Int64.Type}, {"Workstatics", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Grand Total", each [Office] +[Workstatics]), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Week of Month", each Date.WeekOfMonth([Date])), #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each true), #"Added Custom2" = Table.AddColumn(#"Filtered Rows", "Custom", each List.Max(#"Changed Type"[Date])) in #"Added Custom2"Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
looking for filter in power query, don't want to create visual.
Hi Anonymous ,
You could use the function of List.Max and List.Min. Try the code below.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VZDBDcMwDAN3ybtAZUmW3VmK7r9GQ7ZhoA8N+GJRuff78Od4uo19PI5hdqbbeXweIH4R/xEXCb2ZJPkneU87v0aWyD0tJ3KLaJqvOjNMJC8StpDXBvvuiWCmiHqiXkwR9ST/J7aIejLGj19kipQjbwfcAA35CmR2byDTky+7N5IJB7lFUmTDwbRuFKTG4sxulCSx4cxulGRxZnWjuFvD2CYyRRIOyrprkgUH5d01yLbgHt01ScBBVXdNUnBQ3ODzBQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Office = _t, Workstatics = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Office", Int64.Type}, {"Workstatics", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Grand Total", each [Office] +[Workstatics]),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Week of Month", each Date.WeekOfMonth([Date])),
#"Filtered Rows" = Table.SelectRows(#"Added Custom1", each true),
#"Added Custom2" = Table.AddColumn(#"Filtered Rows", "Custom", each List.Max(#"Changed Type"[Date]))
in
#"Added Custom2"
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Thank you So much for your Super guidance
Hi Anonymous ，
I‘m glad to cooperate you to deal with it. :smileywink:
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.