Excel Data Transformation help

[isaudag1]

Hello guys,

I have an excel export whihc has a format as shown and i'm quite new to Power BI to get result like shown.

As shown in below screesnhot, from below main table column'ON' is datetime column & need to count the occurances of StringX for that equipment.

Also i will have many excel files, so how can i automate the transformations for the excel files in a folder.

Any help is much approciated if Power Query is shared for the same.

[Daryl-Lynch-Bzy · Accepted solution]

Hi @isaudag1 , I would suggest using the Power Query to process the data.  Try using the following steps:

1. Select the Range on the Worksheet
2. Choose the "from table/range" button on the Data custom ui menu
3. Select my table has header row
4. When in Power Query, there is a Fill Down function to populate the "Group", "Description", "Area", "Priorty" columns (Table.FillDown)
5. Filter the equipment column to remove the blanks (Table.SelectRows)
6. Use the Group By (Table.Group) to group on the "Group" and "Equipment", count the rows.
7. Re-order the columns.

Note that you create the visual using the results of Step 5.

[isaudag1]

Fill down option is very helpful. Thanks