Need help in creating Table using Power Query

Dharma · April 12, 2021, 4:36pm

Hello Guys,

I am struggling to create a table using Power query in Excel2016 from the attached master sheet, wherein the sheet I was able to create Table1 and Table2 using GroupBy technique in PQ but Table3 is really challenging I was able to create this table using “Percentile” function which is available in MS Excel but unable to create the same using PQ.

Any help with this is really appreciating, I have attached screenshot and excel sheet for your reference.Salary Sheet.xlsx (10.6 KB)

Table1

Table2

Table3

Lucian · April 12, 2021, 6:07pm

Hello @Dharma

For the 3rd table you could use the M Query List.Percentile function.
Just use the “Group by” using MIN as starter and will result a code like this:

let
Source = BaseTable,
#"Grouped Rows" = Table.Group(Source, {"Category", "State", "Job Title"}, {{"Min Salary", each List.Min([Salary]), type nullable number}})
in
#"Grouped Rows"

Then modify the column title and function to accommodate your needs like this:

let
Source = BaseTable,
#"Grouped Rows" = Table.Group(Source, {"Category", "State", "Job Title"}, {{"10 Percentile", each List.Percentile([Salary],0.1), type nullable number}})
in
#"Grouped Rows"

I have attached the complete file.
Salary Sheet_L.xlsx (25.8 KB)

Is the “closest” table I could obtain, except the “AM All State” and “BM All State” that could be easily obtain into another table removing the “State” column from grouping.

Kind Regards,
Lucian

Lucian · April 12, 2021, 6:25pm

Hi again,

To completely solve the 3rd table I had to create 2 intermediate tables to have the correct labels for grouping:

Table3-1:

let
    Source = BaseTable,
    #"Inserted Merged Column" = Table.AddColumn(Source, "New Category", each Text.Combine({[Job Title], [State]}, " "), type text)
in
    #"Inserted Merged Column"

Table3-2:

let
    Source = BaseTable,
    #"Inserted Merged Column" = Table.AddColumn(Source, "New Category", each Text.Combine({[Job Title], [Category]}, " "), type text)
in
    #"Inserted Merged Column"

And the last table (Table3-3) is an “append” of the above tables:

let
    Source = Table.Combine({#"Table3-1", #"Table3-2"}),
    #"Grouped Rows" = Table.Group(Source, {"New Category"}, {{"10 Percentile", each Number.Round(List.Percentile([Salary],0.1),0), type nullable number},{"25 Percentile", each Number.Round(List.Percentile([Salary],0.25),0), type nullable number},{"33 Percentile", each Number.Round(List.Percentile([Salary],0.33),0), type nullable number},{"50 Percentile", each Number.Round(List.Percentile([Salary],0.5),0), type nullable number}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"New Category", Order.Ascending}})
in
    #"Sorted Rows"

And the complete Excel file: Salary Sheet_L2.xlsx (26.9 KB)

Melissa · April 12, 2021, 6:36pm

Hi @Dharma,

Here’s an alternative, just paste each of these in a New Blank Query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdS7bsMwDIXhVyk8ZyApUZcx3Tt1DDJ06JYx748KLpDW+Q+KekiM5AMpXuzLZYu2nbb3+8f909fN69v68DRbX+fb7WX/Y7uelut0JZJuwIWKVyrcnJMs/xmOZRRTjmVEHwPO/Lc77+Xa/uOTq3AzmdY6WHM2zyZYiGjOaB5qFsmsnbXGo9b46TFLjQArihUwF6wMsDnEvCZYVf09nO27I02M3xpdVW7QqbVz5lVb7IUsOS9PsibY5OHUmoTRrYvFPk5X/nzGLOli34qn8xlcWRedw3mqtlS4Jh5ZbwyndsUHw00RL5jWewo3WW42sfGHtuzjUG+e4mAtxYsxwKpihUl7P7LrFw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Empl ID" = _t, State = _t, #"Job Title" = _t, Salary = _t, Category = _t]),
    ChType = Table.TransformColumnTypes(Source,{{"Empl ID", Int64.Type}, {"State", type text}, {"Job Title", type text}, {"Salary", Int64.Type}, {"Category", type text}}),
    GroupAll = Table.Group(ChType, {"Category", "Job Title"}, {{"AllRows", each _, type table [Empl ID=nullable number, State=nullable text, Job Title=nullable text, Salary=nullable number, Category=nullable text]}}),
    AddAll = Table.AddColumn( GroupAll, "Temp", each
        let lSalary = List.Buffer( [AllRows][Salary] ) in
        #table(
            { "Min Salary", "Median Salary", "Max Salary" },
            {
                {
                    List.Min( lSalary ),
                    List.Median( lSalary ),
                    List.Max( lSalary )
                }
            }
        )
    )[[Category], [Job Title], [Temp]],
    ExpandTemp = Table.ExpandTableColumn(AddAll, "Temp", {"Min Salary", "Median Salary", "Max Salary"}, {"Min Salary", "Median Salary", "Max Salary"})
in
    ExpandTemp

.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdS7bsMwDIXhVyk8ZyApUZcx3Tt1DDJ06JYx748KLpDW+Q+KekiM5AMpXuzLZYu2nbb3+8f909fN69v68DRbX+fb7WX/Y7uelut0JZJuwIWKVyrcnJMs/xmOZRRTjmVEHwPO/Lc77+Xa/uOTq3AzmdY6WHM2zyZYiGjOaB5qFsmsnbXGo9b46TFLjQArihUwF6wMsDnEvCZYVf09nO27I02M3xpdVW7QqbVz5lVb7IUsOS9PsibY5OHUmoTRrYvFPk5X/nzGLOli34qn8xlcWRedw3mqtlS4Jh5ZbwyndsUHw00RL5jWewo3WW42sfGHtuzjUG+e4mAtxYsxwKpihUl7P7LrFw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Empl ID" = _t, State = _t, #"Job Title" = _t, Salary = _t, Category = _t]),
    ChType = Table.TransformColumnTypes(Source,{{"Empl ID", Int64.Type}, {"State", type text}, {"Job Title", type text}, {"Salary", Int64.Type}, {"Category", type text}}),
    GroupState = Table.Group(ChType, {"State", "Job Title"}, {{"AllRows", each _, type table [Empl ID=nullable number, State=nullable text, Job Title=nullable text, Salary=nullable number, Category=nullable text]}}),
    AddAll = Table.AddColumn( GroupState, "Temp", each
        let lSalary = List.Buffer( [AllRows][Salary] ) in
        #table(
            { "Min Salary", "Median Salary", "Max Salary" },
            {
                {
                    List.Min( lSalary ),
                    List.Median( lSalary ),
                    List.Max( lSalary )
                }
            }
        )
    )[[State], [Job Title], [Temp]],
    ExpandTemp = Table.ExpandTableColumn(AddAll, "Temp", {"Min Salary", "Median Salary", "Max Salary"}, {"Min Salary", "Median Salary", "Max Salary"})
in
    ExpandTemp

.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdS7bsMwDIXhVyk8ZyApUZcx3Tt1DDJ06JYx748KLpDW+Q+KekiM5AMpXuzLZYu2nbb3+8f909fN69v68DRbX+fb7WX/Y7uelut0JZJuwIWKVyrcnJMs/xmOZRRTjmVEHwPO/Lc77+Xa/uOTq3AzmdY6WHM2zyZYiGjOaB5qFsmsnbXGo9b46TFLjQArihUwF6wMsDnEvCZYVf09nO27I02M3xpdVW7QqbVz5lVb7IUsOS9PsibY5OHUmoTRrYvFPk5X/nzGLOli34qn8xlcWRedw3mqtlS4Jh5ZbwyndsUHw00RL5jWewo3WW42sfGHtuzjUG+e4mAtxYsxwKpihUl7P7LrFw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Empl ID" = _t, State = _t, #"Job Title" = _t, Salary = _t, Category = _t]),
    ChType = Table.TransformColumnTypes(Source,{{"Empl ID", Int64.Type}, {"State", type text}, {"Job Title", type text}, {"Salary", Int64.Type}, {"Category", type text}}),
    GroupAll = Table.Group(ChType, {"Category", "Job Title"}, {{"AllRows", each _, type table [Empl ID=nullable number, State=nullable text, Job Title=nullable text, Salary=nullable number, Category=nullable text]}}),
    GroupState = Table.RenameColumns( Table.Group(ChType, {"State", "Job Title"}, {{"AllRows", each _, type table [Empl ID=nullable number, State=nullable text, Job Title=nullable text, Salary=nullable number, Category=nullable text]}}), {"State", "Category"}),
    NewTable = Table.Combine( { GroupAll, GroupState } ),
    AddAll = Table.AddColumn( NewTable, "Temp", each
        let lSalary = List.Buffer( [AllRows][Salary] ) in
        #table(
            { "10 Percentile", "25 Percentile", "33 Percentile", "50 Percentile" },
            {
                {
                    List.Percentile( lSalary, 0.10 ),
                    List.Percentile( lSalary, 0.25 ),
                    List.Percentile( lSalary, 0.33 ),
                    List.Percentile( lSalary, 0.50 )
                }
            }
        )
    )[[Category], [Job Title], [Temp]],
    ExpandTemp = Table.ExpandTableColumn(AddAll, "Temp", {"10 Percentile", "25 Percentile", "33 Percentile", "50 Percentile"}, {"10 Percentile", "25 Percentile", "33 Percentile", "50 Percentile"})
in
    ExpandTemp

.

Dharma · April 13, 2021, 8:17am

Hi @Melissa @Lucian ,

Am getting the below error when trying to get the List.Percentile figure may be my Excel2016 doesn’t have this function is there any workaround for this?

Expression.Error: The import List.Percentile matches no exports. Did you miss a module reference?

Melissa · April 13, 2021, 8:31am

Create a separate function for the percentile calculation, view source here:

/PercentileInclusive Function

(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

Give this function query a proper name, like: fxPercentile
and call it instead of the List.Percentile function

Please note that this is a Power BI forum and although there are similarities between PQ in Excel and PBI there are differences as well - so post your Excel questions in an Excel forum. Thank you.

I hope this is helpful

EnterpriseDNA · April 15, 2021, 12:35am

Hi @Dharma, did the response provided by @Melissa and @Lucian help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

EnterpriseDNA · April 16, 2021, 12:55am

Hi @Dharma, we’ve noticed that no response has been received from you since the 13th of April. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

EnterpriseDNA · April 19, 2021, 3:52am

Hi @Dharma, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!