Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Write update query in power query

Hi,   I am new to PowerBI & Powerquery and I need some help.   I have below table as source:    job_id step_id run_date run_time duration 09F901AD-0B49-48A7 0 20240723 160000 1650...
Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    Hi, I haven't used your logic, but result is the same as yours. Let me know if it works with real data.

     

    Output:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZLBCsMgEET/xXOE3VlT12NK6TU99FAI+f/fqBFKqKklqyAMwmMeg8viKN0T8XTzdA3JB52iGxzlC0KgCMmRL5RPCSON7NahwfEPbnsT0TaEAySSSgiR/5TVkrpLwiKpPZK6SyaDJKhvycJZJTPUtSTQt2ThzJI4Lsk0Z+719AwJPjxm/ZKULX6+JFIb4Qaip1rY3sLWFiVzS4Wca4G9BVXL+gY=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [job_id = _t, step_id = _t, run_date = _t, run_time = _t, duration = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"step_id", Int64.Type}, {"run_date", Int64.Type}, {"run_time", Int64.Type}, {"duration", Int64.Type}}),
    GroupedRows = Table.Group(ChangedType, {"job_id", "step_id"}, {{"All", each 
        [ a = Table.SelectRows(_, (x)=> x[step_id] = 0){0}?,
          b = Table.AddColumn(_, "schedule_id", (x)=> Text.From(a[run_date]?) & Text.PadStart(Text.From(a[run_time]?), 6, "0"), type text)
        ][b], type table}}, GroupKind.Local,
        (x,y)=> Number.From(y[step_id] = 0) ),
    Combined = Table.Combine(GroupedRows[All])
in
    Combined

     

dufoq3's avatar
dufoq3
Community Champion
2 years ago

Hi Anonymous, check this:

 

Output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY49CoAwDEbv0tnClx9tMyriWgcHQbz/NYyKg2gDgUfgkbdtATYZqB8jBrWouU+hCfBlsCKxOFIHnwtatBT2puLRj3feRHJd4o8kYhdoovsZobi3LpFYNOpc8itSTnwa2eoKVRSv2w8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [job_id = _t, step_id = _t, run_date = _t, run_time = _t, duration = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"step_id", Int64.Type}, {"run_date", Int64.Type}, {"run_time", Int64.Type}, {"duration", Int64.Type}}),
    GroupedRows = Table.Group(ChangedType, {"job_id"}, {{"All", each 
        [ a = Table.SelectRows(_, (x)=> x[step_id] = 0){0}?,
          b = Table.AddColumn(_, "schedule_id", (x)=> Text.From(a[run_date]?) & Text.From(a[run_time]?), type text)
        ][b], type table}}),
    Combined = Table.Combine(GroupedRows[All])
in
    Combined