Create a Pareto Chart Using Power Query

Аватар автора
Excel Таблицы И Базы Данных
#power_query Background: Any previous step can be referenced in a formula to return the entire data structure of that step (usually a table but could be a list, record, or value, etc. ) To convert a table column to a list, put the table name followed by the column name in square brackets [ ] The List.FirstN() function is used to reduce the size of a list to the # of specified items The List.Last() returns the last item on the list Steps to Create a Pareto Chart In Power Query/Excel: 1. Put category data into table and Power Query using “Get Data from Table/Range…” 2. Use Group By operation to create counts of unique categories and sort list in descending order 3. Add index column starting from 1 4. Create running count by adding column with List.Sum() and List.FirstN() functions 5. Reference previous Add Index (AI) step to get list of counts Create Cumulative % list by adding column with List.Last() function 6. Load to Spreadsheet and create combo chart to display data Advanced Editor Code: let Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content], Type" = Table.TransformColumnTypes(Source,{{"Data", type text}}), Rows" = Table.Group(Type", {"Data"}, {{"Count", each Table.RowCount(_), Int64.Type}}), Rows" = Table.Sort(Rows",{{"Count", Order.Descending}}), AI = Table.AddIndexColumn(Rows", "Index", 1, 1, Int64.Type), Custom" = Table.AddColumn(AI, "Running", each List.Sum(List.FirstN(AI[Count],[Index]))), Custom1" =...

0/0


0/0

0/0

0/0