Solved: Excel Data Transformation help

isaudag1 · ‎02-21-2022

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 · ‎02-21-2022

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

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

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

Daryl-Lynch-Bzy · ‎02-21-2022

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

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

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