I’m building a dynamic bucketing system with filtering based on process and program. I’m facing several interesting challenges.
First- bucket sizes are based on the program, so I need dynamic buckets, and bucket labels.
Second- max number of buckets is undefined. I’ll assume 12.
Third- the report requires a trendline for FPY %, plus a trendline over the past 5 buckets
Fourth- Trending is FPY only, which hides some deeper trends.
Fifth- requires most recent bucket upon left side.
Here’s an example:
I’m doing a lookup against a base part number, excluding the dash number, to get a program name. Transforming the part number-dash number into base part number looks like this (Transform window, Text Column group, Split Column button, by delimiter. Split on the last delimiter. Rename the first column to base_part_number, and the right column to dash_part_number.):
#"Duplicated Column" = Table.DuplicateColumn(#"Added Index", "product_number", "product_number - Copy"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "product_number - Copy", Splitter.SplitTextByEachDelimiter({"-"}, QuoteStyle.Csv, true), {"product_number - Copy.1", "product_number - Copy.2"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"product_number - Copy.1", type text}, {"product_number - Copy.2", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"product_number - Copy.1", "base_part_number"}, {"product_number - Copy.2", "dash_part_number"}}),Then I use a lookup against the Tracked Assy Info table, using the base part number to get the program name. That is a two-step process, though it could be one step but less maintainable. First it gets the record index, and then if there was a match it pulls the program name. Otherwise it returns (undefined). Two Custom Columns are used for this. (Transform window, Add column group, Custom Column button.) That’s done here:
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "tracked assy info index", each List.PositionOf(aca_wip_tracked_assy_info[base_part_number], [base_part_number] )),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "program_name", each if [tracked assy info index] = -1 then "(undefined)" else aca_wip_tracked_assy_info[program]{[tracked assy info index]})
I’m then using a foreign-key-style lookup in the M / Power Query code during transformation to provide a rank (index, really) across program groups. I’ll post what that looks like when I’ve got it working – I’ll update this post, as opposed to creating a new post.
Update: Change in plans. This technique introduced a massive delay when updating data. The data was added into the SQL View instead of being added during PowerBI data transformation.
This page contained useful guidance:
https://community.powerbi.com/t5/Desktop/M-Query-lookupvalue/td-p/830337