Challenge No. 17: (Excel formulas also welcome)
Generate the output table.
Solved using: Power Query.
ExcelBI Power Query Challenges
Including all the Power Querry challenges designed by Excel BI.
Dynamic Table Generation Result
Challenge No. 16: (Excel formulas also welcome)
Generate the Result table shown.
Solved using: Power Query (List.Distinct, Table.Pivot, Table.PromoteHeaders).
Top Three Students Report
Challenge No. 15: (Excel formulas also welcome)
Generate Top 3 students in all subjects.
Solved using: Power Query.
Process Step Order Suffixing
Challenge No. 14: (Excel formulas also welcome)
A process gets executed in steps A through D in order (not all steps are necessary in the process but the order defines when the process has started and when it has ended.
Solved using: Power Query (Table.AddColumn, Table.AddIndexColumn).
Fill Missing Sequences Per ID
Challenge No. 12: (Excel formulas also welcome)
If there is a sequence missing for a given ID, fill in the missing sequence and repeat the row above for all other columns.
Solved using: Power Query (Table.AddColumn, Table.ExpandTableColumn, Table.Group).
Flexible Result Table Creation
Challenge No. 11: (Excel formulas also welcome)
Generate Result table as shown.
Solved using: Power Query (List.Distinct, Table.AddColumn, Table.Pivot).
Dynamic Result Table Generation
Challenge No. 10: (Excel formulas also welcome)
Generate Result table as shown.
Solved using: Power Query (Table.AddColumn, Table.SelectRows, Table.Sort).
Replace Words in Original Text
Challenge No. 9: (Excel formulas also welcome)
There is a table which has Original Text and there is another table which contains the list of words which need to be replaced.
Solved using: Power Query (List.Transform, Table.AddColumn, Table.ReplaceValue).
Clean Data and Restructure Table
Challenge No. 8: (Excel formulas also welcome)
Clean the data and generate the result table as shown.
Solved using: Power Query (Table.AddColumn, Table.TransformColumns).
Count Unique Subjects Per Row
Challenge No. 6: (Excel formulas also welcome)
Count the no.
Solved using: Power Query (Table.TransformColumns).