Hi @Jimmy801 ,
This is with respect to my last post, on `Table of Tables`. I am trying to find an easier & concise way to do all the Percentile calculations and column additions in each table in the `Table of tables` and then finally Combine all the tables. The way i am able to achieve it is very long and tedious and involves the query editor to add steps like, Combining tables first, then unpivot, then pivot and group by, ExpandColumns etc.
Here is the sample code i created in Power BI Editor (though my original report is i am working on Excel 2016 though) for your perusal. Please note that each table in `Source` shown here has been transformed through multiple steps to clean each table.
let
Source = {Table1,Table2},
ListOfTables = Source,
CombinedTables = Table.Combine(ListOfTables),
TransformTypes = Table.TransformColumnTypes(
CombinedTables,
List.Zip({
List.Buffer(List.Difference( Table.ColumnNames(CombinedTables), {"Code", "Metric"} )),
{type number, type number, type number}
})
),
UnpivotedOtherColumns = Table.UnpivotOtherColumns(TransformTypes, {"Code", "Metric"}, "Subgroup", "Value"),
PivotedOnMetric = Table.Pivot(UnpivotedOtherColumns, List.Distinct(UnpivotedOtherColumns[Metric]), "Metric", "Value"),
UNIQ_P = PercentileInclusive(List.Buffer(PivotedOnMetric[UNIQ]),0.8),
ND_P = PercentileInclusive(List.Buffer(PivotedOnMetric[ND]),0.8),
UNIQ_TOP = Table.AddColumn(PivotedOnMetric,"Top UNIQ", each if [UNIQ]>UNIQ_P then "Top" else ""),
ND_TOP = Table.AddColumn(PivotedOnMetric,"Top ND", each if [ND]>ND_P then "Top" else ""),
NestedJoin = Table.NestedJoin(UNIQ_TOP,"Code", ND_TOP,"Code","ALL", JoinKind.Inner),
ExpandedALL = Table.ExpandTableColumn(NestedJoin, "ALL", {"Top ND"}, {"Top ND"})
in
ExpandedALL
PercentileInclusive function code:
//PercentileInclusive Function
let
Source = (inputSeries as list, percentile as number) =>
let
SeriesCount = List.Count(inputSeries),
PercentileRank = percentile*(SeriesCount-1)+1, //percentile value between 0 and 1
PercentileRankRoundedUp = Number.RoundUp(PercentileRank),
PercentileRankRoundedDown = Number.RoundDown(PercentileRank),
Percentile1 = List.Max(List.MinN(inputSeries,PercentileRankRoundedDown)),
Percentile2 = List.Max(List.MinN(inputSeries,PercentileRankRoundedUp)),
Percentile = Percentile1+(Percentile2-Percentile1)*(PercentileRank-PercentileRankRoundedDown)
in
Percentile
in
Source
Please let me know how this code can be optimized and made concise for performance.
