The New Hotness (Custom Matrix Hierarchy)

OK, this one is a bit complex and is in many ways a different spin on the Disconnected Table Trick. So, this recipe is designed to fix the issue where you have a matrix with a column or two in it a...

Impossible.pbix57 KB

Other

CNENFRNL

Community Champion

6 years ago

Another piece from your colleciton of ingenious works!

Please produce Customer Hierarchy table in PQ, if you don't mind.😉

let
    Source = Table.FromRecords(
        List.Generate(
            () => {Date.StartOfYear(Date.AddYears(DateTime.LocalNow(), -1))},
            each Date.Year(_{0}) <= Date.Year(Date.AddYears(DateTime.LocalNow(), 0)),
            each {Date.AddMonths(_{0}, 1)},
            each [Year = Date.Year(_{0}), Month = Date.Month(_{0}), MonthName = Date.MonthName(_{0})]
        )
    )
    &
    #table({"Year", "Month", "MonthName"}, {{"Other", "LY", 13}, {"Other", "PY", 14}})
in
    Source

Greg_Deckler
Community Champion
6 years ago
CNENFRNL - Oh yeah, I like that! Very nice! I wish my Power Query fu was stronger sometimes! 🙂