Single column containing two types for references with diff prefix but dynamic numbers changing

[rinaredelinghuy]

I'm very new to PowerBI and tried to go through the posts but are just not able to find the solution to my issue.

Scenario:

I have a reference column containing two diff types of attributes with, the one has a prefex of R:(number)(Picking ref) & NB:(number)(checking ref)

Prefex R:25423 , R52633 (multiple numbers) and the same applies to NB:

I need to calculate the sum of the confirmed total pertaining to R: based on the create date per hour as well as the sum of the check total pertaining to NB: based on the create date per hour.

Not sure if I've explained this properly.  Your help in this instance will be much appreciated

Below the data columns

Regards

Rina

[BA_Pete · Accepted solution]

Hi @rinaredelinghuy ,

The first thing I'd do would be to create new columns in Power Query with extra descriptors that I could later use to simplify writing measures. For example:

New columns in PQ:

referenceType =
if Text.StartsWith([ReferenceNumber], "R") then "Picking"
if Text.StartsWith([ReferenceNumber], "NB") then "Checking"
else "Other"

createDateOnly =
Date.From([CreateDate])

createDateHourOnly =
Time.StartOfHour([CreateDate])

This then makes writing explicit measures and creating visuals super-easy. Sums are just:

_confirmedPickingQty =
CALCULATE(
    SUM(yourTable[ConfirmedQuantity]),
    yourTable[referenceType] = "Picking"
)

Or you could even just use a simple SUM(yourTable[ConfirmedQuantity]) measure, and use your new [referenceType], [createDateOnly], and [createDateHourOnly] dimensions in a visual to have the data split out correctly.

Pete

[BA_Pete · Accepted solution]

Ok, that actually simplifies things a bit, I think.

All we need is the [ReferenceNumber] value without the 'R:' or 'NB:' portion. You may already have this as [order no], [product], or [batch] but, in case you don't, you can get it into another new column something like this:

refOnly = Text.AfterDelimiter([ReferenceNumber], ":")

Once you have that, you just need two simple measures:

_noofPicked = SUM(yourTable[ConfirmedQuantity])

// and

_noofChecked = SUM(yourTable[CheckQuantity])

You should now be able to acccurately visualise what you need using any/all of your existing and new dimensions, and these measures.

If you're struggling to get the output you want with what we've created so far, just post an example of exactly what you want it look like on here and I'll help you out.

Pete

[rinaredelinghuy]

Hi Pete,

With reference to the above, I've now encountered a scenario where I have x1 NB reference and x2 R reference belonging to the same IssuelineID.  The NB qty is now inserted on both R(doubling up), R reference should be equavelant to NB.  How do I either split the NB qty between the number of R references or simply just sum to the first R reference with the same  issuelineid, ignoring the other R references ?

[BA_Pete]

Hi @rinaredelinghuy ,

You just need to find the dimension that makes your two R rows distinct from each other. I'd guess this is probably a product code, so, on your 'groupIssueLineID' step, just add your product column into the grouping, like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8gnQNTSwNDJV0lEKyEzOzsxLB7KCrMzMjS2BDEMLCwMzEG1kDCQNwDhWh1hdlpaW2HQZ49YFkjI1MMClyzkjFabNzwmoz8QcQ58xGKO5EUOfEYYrjcA4NhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [OrderNumber = _t, RefType = _t, ReferenceNumber = _t, IssueLineId = _t, ProductID = _t, ConfirmedQuantity = _t, CheckQuantity = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"OrderNumber", type text}, {"RefType", type text}, {"ReferenceNumber", type text}, {"IssueLineId", Int64.Type}, {"ConfirmedQuantity", Int64.Type}, {"CheckQuantity", Int64.Type}, {"ProductID", Int64.Type}}),
    groupIssueLineId = Table.Group(chgTypes, {"IssueLineId", "ProductID"}, {{"data", each _, type table [OrderNumber=nullable text, RefType=nullable text, ReferenceNumber=nullable text, IssueLineId=nullable number, ProductID=nullable number, ConfirmedQuantity=nullable number, CheckQuantity=nullable number]}, {"newConfirmedQty", each List.Sum([CheckQuantity]), type nullable number}}),
    expandDataColumn = Table.ExpandTableColumn(groupIssueLineId, "data", {"OrderNumber", "RefType", "ReferenceNumber", "ConfirmedQuantity", "CheckQuantity"}, {"OrderNumber", "RefType", "ReferenceNumber", "ConfirmedQuantity", "CheckQuantity"})
in
    expandDataColumn

This gives me the following output. Note that [productID] 999 has matching NB/R rows, but [productID] 123 does not, so it returns a zero:

Pete

[rinaredelinghuy]

Hi Pete,

Thank you for the prompt response.  The problem I've got is that the details are the same, multiple instructions might have been created to pick (R:) the products (same issuelineID), then checked, when the NB was created.  See below example (no unique details apart from the reference R: or NB:

[rinaredelinghuy]

If IssueLineID for NB:  is same as R: , then take sum of CheckQty for IssueLineID and insert on R:(same IssueLineID) ConfirmQty

[BA_Pete]

Hi,

I'm not really following I'm afraid. Would it be possible to show me a tiny example of what the source table looks like before, and then an example of how you want it to look after please?

It sounds like you're wanting to overwrite data in the table row, but this is rarely the solution to anything.

Pete

[rinaredelinghuy]

I want to create a new ConfirmedQty Column against the R:, displaying the sum of the CheckQty for the NB: linked via the IssueLineID.

In the example "AS IS" R:6739, IssueLineID 18803 & 18806 confirmed qty = zero. NB:6747 with IssueLineID 18803 is related to R:6739 with IssueLineID 18803, therefore I require the sum of CheckQuantity for NB:6747 with IssueLineID 18803 to be displayed as the ConfirmedQty for R:6739, IssueLineID

"AS IS"

TO BE

[BA_Pete · Accepted solution]

Ok, give this a go. Just paste this into a blank query in Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8gnQNTSwNDJV0lEKyEzOzsxLB7KCrMzMjS2BDEMLCwMzIG0AxrE6cPXGuNUb41LvnJEK0+DnBNRhYo6kA4KRdJhi02GE5CYjMI6NBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [OrderNumber = _t, RefType = _t, ReferenceNumber = _t, IssueLineId = _t, ConfirmedQuantity = _t, CheckQuantity = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"OrderNumber", type text}, {"RefType", type text}, {"ReferenceNumber", type text}, {"IssueLineId", Int64.Type}, {"ConfirmedQuantity", Int64.Type}, {"CheckQuantity", Int64.Type}}),
    groupIssueLineId = Table.Group(chgTypes, {"IssueLineId"}, {{"data", each _, type table [OrderNumber=nullable text, RefType=nullable text, ReferenceNumber=nullable text, IssueLineId=nullable number, ConfirmedQuantity=nullable number, CheckQuantity=nullable number]}, {"newConfirmedQty", each List.Sum([CheckQuantity]), type nullable number}}),
    expandDataColumn = Table.ExpandTableColumn(groupIssueLineId, "data", {"OrderNumber", "RefType", "ReferenceNumber", "ConfirmedQuantity", "CheckQuantity"}, {"OrderNumber", "RefType", "ReferenceNumber", "ConfirmedQuantity", "CheckQuantity"})
in
    expandDataColumn

This gives me the following output:

This works at the [IssueLineId] level but, if you had more dimensions, such as [product] etc. you'd want to add them into the groupRows step to ensure you were getting the [CheckQuantity] value over the correct subset of rows.

Pete

[rinaredelinghuy]

Hi Pete,

Thank you for sharing this solution, how do I get it now to be dynamic to run accross the IssueLineID column?

[BA_Pete]

You just need to swap out the 'Source' and 'chgTypes' steps with your own source details, and make sure the rest of the code refers to the correct column names as they appear in your actual source (which I'm hoping they should, as I've tried to use exactly the same names as your example).

This part is a bit more difficult for me to help with as I obviously don't have access to your actual sources, but let me know if you get stuck and I'll help you out where I can.

Pete

[rinaredelinghuy]

Hi Pete,

I did not express my requirement properly to you, apologies for that. After further investigation I've found a common denominator - issuelineID

R: is linked to the respective NB: via the IssueLineID

I require the sum of CheckQty for all NB: linked to a specific R: via the IssueLineID, to be the sum of the ConfirmQty for the R:  (see below data sample).  I hope I'm expressing myself correctly this time round.

[BA_Pete · Accepted solution]

Hi @rinaredelinghuy ,

The first thing I'd do would be to create new columns in Power Query with extra descriptors that I could later use to simplify writing measures. For example:

New columns in PQ:

referenceType =
if Text.StartsWith([ReferenceNumber], "R") then "Picking"
if Text.StartsWith([ReferenceNumber], "NB") then "Checking"
else "Other"

createDateOnly =
Date.From([CreateDate])

createDateHourOnly =
Time.StartOfHour([CreateDate])

This then makes writing explicit measures and creating visuals super-easy. Sums are just:

_confirmedPickingQty =
CALCULATE(
    SUM(yourTable[ConfirmedQuantity]),
    yourTable[referenceType] = "Picking"
)

Or you could even just use a simple SUM(yourTable[ConfirmedQuantity]) measure, and use your new [referenceType], [createDateOnly], and [createDateHourOnly] dimensions in a visual to have the data split out correctly.

Pete

[rinaredelinghuy]

Hi Pete,

Thank you so much, works perfectly.

Just one more issue I'm faced with now:

Whilst picking is executed R: will have a confirmed qty against it, but as soon as it moves over to checking, a NB no. is created with the qty checked, automatically zeroing out the pick confirmed qty.

How do I get the checked qty to be inserted back into the confirmed qty, as both these actions are measured seperately from a productivity point of view?

[rinaredelinghuy]

To summarize I require a solution to:

if dropsequence for checkqty is the same as confirmedqty then update confirmedqty with checkqty else leave as is

The sum for the checkqty(NB) must equal the confirmedqty(R) (in some instances the checkqty might have more lines than the confirmedqty, but the sum must match overall

[BA_Pete]

*EDIT* Never mind, I see what you mean now. A new row is created for the checking portion of the same order. I assume that somewhere in your source table there is a product code column, so each product code gets its own picking row against the order, and its own checking row?

Ignore this:

I'm not sure I understand what you mean by "automatically zeroing out the pick confirmed qty".

Do you mean that the actual data in the [ConfirmedQuantity] column is overwritten with a zero at some point?

Pete

[rinaredelinghuy]

*EDIT* Never mind, I see what you mean now. A new row is created for the checking portion of the same order. I assume that somewhere in your source table there is a product code column, so each product code gets its own picking row against the order, and its own checking row?

Correct.

Instructions are generated for a picking actions, once done, then checking action is required.  Unfortunately these instructions are shared accross the two actions (R: vs NB:) 

Picking is executed based on the order no; product;batch;area;picklocation - once prod's have been picked it needs to be verified that it matches the order qty, ensuring the right product vs batch has been picked, hence checking validation is required.

[BA_Pete · Accepted solution]

Ok, that actually simplifies things a bit, I think.

All we need is the [ReferenceNumber] value without the 'R:' or 'NB:' portion. You may already have this as [order no], [product], or [batch] but, in case you don't, you can get it into another new column something like this:

refOnly = Text.AfterDelimiter([ReferenceNumber], ":")

Once you have that, you just need two simple measures:

_noofPicked = SUM(yourTable[ConfirmedQuantity])

// and

_noofChecked = SUM(yourTable[CheckQuantity])

You should now be able to acccurately visualise what you need using any/all of your existing and new dimensions, and these measures.

If you're struggling to get the output you want with what we've created so far, just post an example of exactly what you want it look like on here and I'll help you out.

Pete

[rinaredelinghuy]

Thanks Pete, works perfectly!

[BA_Pete]

No problem at all, glad we got there in the end 🙂

Have a great weekend!

Pete