Skip to main content

Notifications

Power Automate - Building Flows
Unanswered

Optimizing big data into small data by using some filters in SharePoint excel file

Posted on 14 Nov 2024 05:58:36 by 150
Hi Experts ,
 
I am working on an excel file having 330k rows and i want to optimize with some filters.
Below the sample  input and output i want to work on.
 
INPUT :
Country SBU Grade Status
US AA A Completed
US AA B Completed
US AA B Completed
US AA C Completed
US AA B Completed
UK BB B Completed
UK BB B Overdue
UK BB B Completed
UK BB C Overdue
UK BB B Overdue
 
 
OUTPUT :
Country SBU Grade             %      Completed
          Status
           Total
          Status
US AA B 100.00% 3 3
UK BB B 50.00% 2 4
 
 
Requirement is to filter Grade B from the above table and count the completed status and calculate % group by Country & SBU.
Please help to give idea on creating a flow .
 
Thanks
DK 
  • Chriddle Profile Picture
    Chriddle 6,991 on 26 Nov 2024 at 10:43:29
    Optimizing big data into small data by using some filters in SharePoint excel file
    To write your data to an Excel file, just use the Excel connector "Add a row into a table".
    If this takes too long due to too many rows, create a CSV from your data and try Convert CSV files to Excel workbooks.
  • DineshK_ Profile Picture
    DineshK_ 150 on 26 Nov 2024 at 10:27:44
    Optimizing big data into small data by using some filters in SharePoint excel file
     
    Should be Global_x0020_completion :
     
     
    Post applied this code then the result is good .
     
     
     
    Now i want to get these data into excel file like below  , can we use create xml table action ?
     
  • Chriddle Profile Picture
    Chriddle 6,991 on 26 Nov 2024 at 08:01:50
    Optimizing big data into small data by using some filters in SharePoint excel file
    Search the output of "Compose-XML" for the correct node name and value (something like <Status>Completed</Status>)
     
  • DineshK_ Profile Picture
    DineshK_ 150 on 26 Nov 2024 at 07:26:50
    Optimizing big data into small data by using some filters in SharePoint excel file
    Hi Chriddle,
     
    I have updated the xpath and Completed status is not counted but only total status is calculated..
     
     
  • Chriddle Profile Picture
    Chriddle 6,991 on 21 Nov 2024 at 13:33:50
    Optimizing big data into small data by using some filters in SharePoint excel file
    That's not the XML node name. You need to change the left side:
     
    'SBU_x002F_GBL="', item()['SBU/GBL'], '" and ',
  • DineshK_ Profile Picture
    DineshK_ 150 on 21 Nov 2024 at 12:32:33
    Optimizing big data into small data by using some filters in SharePoint excel file
     
    No luck .. we are getting errors when doing count after changed the SBU/GBL item in xpath ..
     
     
     
     
     
  • Chriddle Profile Picture
    Chriddle 6,991 on 21 Nov 2024 at 10:12:17
    Optimizing big data into small data by using some filters in SharePoint excel file
    In XML the node name is not SBU/GBL but SBU_x002F_GBL
     
    This either needs to be adjusted in the XPath expressions or this property name needs to be changed beforehand.
    I recommend the latter. Who knows where else the slash causes problems.
     
     
  • DineshK_ Profile Picture
    DineshK_ 150 on 21 Nov 2024 at 06:12:02
    Optimizing big data into small data by using some filters in SharePoint excel file
    Hi Chriddle,
     
    Actually my work requirement is to filter grade F in my data .
    But Grade B is just when i am giving sample data to you . 
    I have applied F in all my actions based on my scenario.
     
     
     
    Now the percent calculation action got failed due to value 0 cannot be div by 0 .
    Then i need to get & save the filtered rows into some new file.
  • Chriddle Profile Picture
    Chriddle 6,991 on 20 Nov 2024 at 21:33:56
    Optimizing big data into small data by using some filters in SharePoint excel file
    Requirement is to filter Grade B from the above table and count the completed status 
    Why is this item of grade F? Did you change the filter?
     
    If so, keep in mind, that you also have to change the type of items to count in "Map Status" and "Map Total":
    'count(//Item[',
        'Country="', item()['Country'], '" and ',
        'SBU="', item()['SBU'], '" and ',
        'Grade="B"',
    '])'
     
  • takolota1 Profile Picture
    takolota1 4,748 on 20 Nov 2024 at 20:22:12
    Optimizing big data into small data by using some filters in SharePoint excel file
    If you need this to work with Excel data / can’t convert to a CSV, then version 1b (V1b) of this template can help you get more than 100,000 rows pretty quickly

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

November 2024 Newsletter…

November 2024 Community Newsletter…

Community Update Oct 28…

Power Platform Community Update…

Tuesday Tip #7 Community Profile Tips…

Welcome to a brand new series, Tuesday Tips…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 143,129

#2
RandyHayes Profile Picture

RandyHayes 76,308

#3
Pstork1 Profile Picture

Pstork1 63,797

Leaderboard