Forum Discussion

mikem3's avatar
mikem3
New Member
3 years ago

Creating Buckets to add up the time between changes

My dataset has 3 columns that can look like this.    STATUS CHANGEDATE WONUM ASSIGN 5/1/2023 WO-23521 IN PROGRESS 5/2/2023 WO-23521 SUBMITTED 5/10/2023 WO-23521 IN REVIEW 5/...
Need Help
Vijay_A_Verma's avatar
Vijay_A_Verma
Most Valuable Professional
3 years ago

Use this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcgwO9nT3U9JRMtU31DcyMDIGMsP9dY2MTY0MlWJ1opU8/RQCgvzdg1yDg8GqjLCqCg518vUMCXF1gZhkgMuoINcwT9dwiCLs9gW5erk6ww0yJspNhibEOMocpkgBn6sssKtyDADaGAYzyhKLolgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [STATUS = _t, CHANGEDATE = _t, WONUM = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"STATUS", type text}, {"CHANGEDATE", type date}, {"WONUM", type text}}),
    BuffList = List.Buffer(#"Changed Type"[CHANGEDATE]),
    DiffList = List.Generate(()=>[x=Duration.Days(BuffList{1}-BuffList{0}),y=0], each [y]<List.Count(BuffList), each [y=[y]+1, x=try Duration.Days(BuffList{y+1}-BuffList{y}) otherwise 0], each [x]),
    result = Table.FromColumns(Table.ToColumns(#"Changed Type") & {DiffList}, Table.ColumnNames(#"Changed Type") & {"Diff"}),
    #"Grouped Rows" = Table.Group(result, {"STATUS"}, {{"Total Duration", each List.Sum([Diff]), type number}}) 
in
    #"Grouped Rows"
mikem3's avatar
mikem3
New Member
3 years ago

This is fantastic thank you.  The issue I run into is it I am not sure of the correct way to update the source from 

Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcgwO9nT3U9JRMtU31DcyMDIGMsP9dY2MTY0MlWJ1opU8/RQCgvzdg1yDg8GqjLCqCg518vUMCXF1gZhkgMuoINcwT9dwiCLs9gW5erk6ww0yJspNhibEOMocpkgBn6sssKtyDADaGAYzyhKLolgA"

 to my actual source which is a SQL database with a query to my dataset.  I tried adding how I thought it would work but I get an agument error so I'm probably putting missing/inserting an extra ( or , somewhere

 

Source = Sql.Database("ogssqlbi03ptul", "public_rpt", [Query="select * from maximo.MAXIMO_OGSLOCUSWOSTATUS"], let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [STATUS = _t, CHANGEDATE = _t, WONUM = _t]),

 

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    3 years ago

    Pull your data from DB through PQ and a source line will be generated. Just paste this source line replacing source line in my code.