Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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...
Need Help
  • v-xuding-msft's avatar
    v-xuding-msft
    6 years ago

    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.

v-xuding-msft's avatar
v-xuding-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi Anonymous ，

How do you want to filter? Just show the earliest date?

  • You could put date to a card visual and shown as earliest or latest date.

  • Or you could create formulas.
Earliest date = MIN( 'Table'[Date])
Latest date = MAX('Table'[Date])

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.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

looking for filter in power query,  don't want to create visual. 

  • v-xuding-msft's avatar
    v-xuding-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago
      Thank you So much for your Super guidance
      • v-xuding-msft's avatar
        v-xuding-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        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.