Forum Discussion

isaudag1's avatar
isaudag1
New Member
4 years ago
Solved

Excel Data Transformation help

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.

 

Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.