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

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

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

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