Merge dates across quantity and text flag

mdalton2100 · February 22, 2024, 11:59pm

I think the below screenshot captures the nature of the underlying data and what I am struggling to achieve (table next to green check). For each month, the underlying data separately captures a quantity and a text value for every month. I simply need to be able to generate report by the shown text value. I have tried unpivoting (2x), but it just duplicates the data. Thanks!

[Same date with both Amount and Tag _PBI.pbix|attachment]
Same date with both Amount and Tag.xlsx (19.5 KB)
(upload://rr1iv4SGr5546gay5GihU9SYO8H.pbix) (113.2 KB)

Melissa · February 23, 2024, 7:12am

Hi @mdalton2100,

You can achieve that using mostly the UI, see this approach

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRCkotS80rTcXBciwuTi0uzk3NK8HDidUBm2Soa6hrZAJkGMEYxjAGTimQVufS4pL83NQiBUeguImBgYECSCGUNoXSfqnlQDI4oygzLzszLx3Idi/KLwexUIxwAhkN1QIzytgUQmPTjCyKYpAzUMoMaoA5lLZAcQvCCLhLYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t]),
    RemoveFirst = Table.Skip(Source,1),
    PromoteHeaders = Table.PromoteHeaders(RemoveFirst, [PromoteAllScalars=true]),
    UnpivotOthers = Table.UnpivotOtherColumns(PromoteHeaders, {""}, "Attribute", "Value"),
    RemoveCols = Table.RemoveColumns(UnpivotOthers,{""}),
    ReplaceValue = Table.ReplaceValue(RemoveCols,each [Attribute],each Text.BeforeDelimiter([Attribute], "_"),Replacer.ReplaceValue,{"Attribute"}),
    GroupRows = Table.Group(ReplaceValue, {"Attribute"}, {{"t", each Table.FromColumns( { List.Alternate([Value], 1, 1), List.Alternate([Value], 1, 1, 1)})}}),
    ExpandTable = Table.ExpandTableColumn(GroupRows, "t", {"Column1", "Column2"}, {"Column1", "Column2"}),
    ChType = Table.TransformColumnTypes(ExpandTable,{{"Column2", Currency.Type}}),
    PivotCol = Table.Pivot(ChType, List.Distinct(ChType[Attribute]), "Attribute", "Column2", List.Sum)
in
    PivotCol

With this result

I hope this is helpful

mdalton2100 · February 24, 2024, 7:37pm

Well thank you Melissa… I applied the code, it works. In the interim, I did take the UI approach of splitting the ‘Revenue’ and ‘Assessment’ columns into separate tables, unpivoting them, creating a lookup key, and then doing a merge query. Works fine, and also brings in Customer (but I’m sure you’d know how to do that with code! … not my skillset). For the folks watching, below screenshot should give you an idea of my “UI” (user interface) approach.

Aside observation: The ‘Assessment’ section is being calculated in Excel using cell-based sum-ifs (checking calc’d $ deltas MoM) … I would like to do that using DAX code instead, which I assume is possible, right? I will likely post a new topic / question on that.

Thanks again!