Best way to create a new table from existing table. Power Query or Power BI Desktop
Excuse this if it seems a very obvious question, but I'm new to Power BI, trying to migrate from using Excel.
I have "order line data" imported from csv into a table and it looks a bit like:
ORDER, ITEMCODE, QTY
order1, itemA, 2
order1, itemB, 5
order2, itemC, 1
order3, itemA, 10
order3, itemD, 2
In Power BI Desktop, I can present a Table summarising this such as
OrderID, Num Order Lines, Qty Ordered
order1, 2, 7
order2, 1, 1
order3, 2, 12
This displays what I want, but I want to work with this table, so that I can see how many orders have 1 line, how many have 2-5 lines, have many have 1 item, etc.
I figure I need to create my new table in data, rather than just display it, but should I create this using Power Query or the "New Table" functionality (button?) in Power BI Desktop? I've started doing both, but not sure which is the best way forward.
As I said, I'm new to this, although I have used Power Query and Power Pivot successfully in Excel. When I get this working, I want it to be a template, so that I can just import new order line data and Power BI Desktop will do the summary stuff for me, so wanted to make sure I'm going down the correct route first.