Calculating a increase in faults across multiple Days/Dates

[Nida]

My table has a dates, days of the week and count of faults column. I was wondering if someone could advise me regarding how I can calculate the number of fault increases across Friday and Saturday. For example 

Date              Days       Number of faults    Increase in Fault

13.01.2023     Friday            400                      500

14.01.2023    Saturday        900

Also is there a way to filter such that only those faults with an increase of 500 on Saturday are displayed?

Really appreciate any support,

Many thanks

[Nida]

Hi @jbwtp,

That's completely fine you do not need to apologise you have already been a great help going out of your way go help me. I really appreciate it. I had been trying use Power BI for work and have learnt a lot from your answers.

Many thanks 

[Nida]

@jbwtp Hi John,

I really appreciate your reply been trying many script for a while and haven't gotten what I was aiming for so really appreciate you providing the above. Will try this out tomorrow and update the post. 

Many thanks 

[jbwtp]

Hi @Nida,

This code brings "closing balance" to the dataset. This is straightforward to calculate/filter the increase from there:

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTWMzDUMzIwMlbSUXIrykxJrAQyTAwMlGJ1gLImSLLBiSWlRRB5S5B8LAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Days = _t, #"Number of faults" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Days", type text}, {"Number of faults", Int64.Type}}),
    Combine = Table.ToColumns(#"Changed Type") & {List.Skip(#"Changed Type"[Number of faults])},
    Format = Table.FromColumns(Combine, Value.Type(Table.AddColumn(#"Changed Type", "Closing", each null, type number )))
in
    Format

Cheers,

John

[Nida]

Hi @jbwtp John,

I had just saved the above script as a .json file but received the following error message.

I was wondering if you may be able to advise me regarding this.

Many thanks,

Nida

[jbwtp]

Hi Nida,

this is not a JSON script. This is M code for PQ.

To test it you need to create a blank query in PQ:

and then copy the code into the query using Advanced Editor.

Kind regards,

John

[Nida]

Hi @jbwtp John,

I have managed to apply your code, I very much appreciate you uploading it above. I was wondering if there was to display Friday and Saturday output across 2 months data, I have data from November to early Jan and for some reason the output is not showing the difference but rather with the dates I used in the example for which data I do not have as the data is for 01.11.2022 - 04.01.2023

Many thanks really appreciate it.

Kind regard,

Nida

[Nida]

Thanks @jbwtp John,

Apologies I am new to this system and have mainly been using it to upload excel based datasets to produce visuals and trends.

Really appreciate you breaking it down above will try this later today. 

Many thanks 

[jbwtp]

Hi Nida,

if you have the actual data as query [e.g.] called DataTable the code that I provided should be changed to:

    Source = DataTable,
    Combine = Table.ToColumns(Source) & {List.Skip(Source[Number of faults])},
    Format = Table.FromColumns(Combine, Value.Type(Table.AddColumn(Source, "Closing", each null, type number )))
in
    Format

Assuming that the column names and types are the same as in your example.

The code above takes the ourput of the DataTable query and adds the "Closing" column.

Cheers,

John

[Nida]

Hi @jbwtp John,

Many thanks once again for your time and the code. Due to the confidentiality of the data I had to reword them in my example. I have taken your new code above and changed the table name and faults accordingly to match my table and column name. I may have misunderstood so I apologise. Where it says closing my main goal though I am not sure if this is feasible was too look at these incidents against a unique identifer I have in the shaded column and too see what the increase was on Saturday against Friday but I am a little unsure as to what the closing column output numbers represent. I do apologise that I have already taken up so much of your time. So what I mean is say for example had the hidden unique column been named fruits so I have apples and oranges. I would like to see;

Fruit name  Incident Count   Date               Days           Increment/Decrement

Oranges       50                      30.12.2022     Friday          150

Oranges       200                    31.12.2022     Saturday

In my data Fruits and it's values are different but I'm not sure if I am able to apply this principle just to change the data headings in the code.

[jbwtp · Accepted solution]

Hi @Nida,

Sorry for the delay, I was working on a deadline project in the last few weeks.

I think you get the idea right. "Closing" column is the "closing balance" (number of faults) for the day as you have it as runing total. It eaquals to the number of faults on the next day. Of course your rows should be sorted by the Date column.

You can apply if to the mixed unique items in the same table by using Table.Group.

This example a bit messy, but it gives you an idea where to go:

let 
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTWMzDUMzIwMlbSUXIrykxJrAQyTAwMgKR/UWJeeqpSrA5QmQmSsuDEktIiiEJLDIVYzTMFqXIsKMjBb5qRAZK6WAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Days = _t, #"Number of faults" = _t, Fruit = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Days", type text}, {"Number of faults", Int64.Type}}),
vt = Value.Type(Table.AddColumn(#"Changed Type", "Closing", each null, type number )),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Fruit"}, {{"Data", each 
    let 
            Combine = Table.ToColumns(_) & {List.Skip(_[Number of faults])},
            Format = Table.FromColumns(Combine, vt)
        in Format
    }}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Date", "Days", "Number of faults", "Closing"}, {"Date", "Days", "Number of faults", "Closing"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Data",{"Date", "Days", "Number of faults", "Fruit", "Closing"}),
    Custom1 = #table(vt, Table.ToRows(#"Reordered Columns")),
    #"Added Custom" = Table.AddColumn(Custom1, "Increase", each [Closing]-[Number of faults])
in
    #"Added Custom"