Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
lunako
Frequent Visitor

Compare the daily updated Excel sheets

Data source is Folder, the same format of Excel file is exported and replaced daily in the folder.

The visual only need the values in the latest file but if the value is modified since yesterday, the value want to be highlited in red.

 

Yesterday's Excel file

ORDER #QTYETA
00001106/1/2022
00002206/1/2022
00003306/2/2022

 

Today's Excel file

ORDER#QTYETA
00001156/1/2022
00003306/5/2022
00004406/10/2022

 

Table visual wants to show:

ORDER#QTYETA
00001156/1/2022
00003306/5/2022
00004406/10/2022

 

Feeling I need 1) set the datasource = the folder, 2) append the data, 3)Sort by the Excel import date and compare the rows by the Order# of the latest vs the second latest, set the flag which columns have different, 4) Cell font conditional format by the flag.

I need your help how to realize 3) step, or any better workaround?

 

Thank you so much!

2 REPLIES 2
lunako
Frequent Visitor

Thank you. Ok, need two tables. Now I am trying to find how to move the data from the updated table (today's data) -> to the original table (yesterday's data) automatically when daily refresh. Assume I need the move dataset command like below in red before transforming the latest Excel file. Or set the refresh order, "duplicate the table" should run before "refresh the updated table".

 

let
Source = Folder.Files("FOLDERPATH"),
ORIGINAL DATASET = UPDATED DATASET, (<- move the table/dataset, but unable to find how to write in M. )
#"then continue the transform on the Source (= today's data)...

 

Two tables and its data:

Operation DayORIGINAL TABLE (Yesterday's data)UPDATED TABLE (Today's data)
DAY 2DAY 1 dataDAY 2 data
DAY 3DAY 2 dataDAY 3 data
DAY 4DAY 3 dataDAY 4 data ... continue

 

This answer says this cannot be done by Power BI. Anyone comes up with any workaround?

v-eqin-msft
Community Support
Community Support

Hi @lunako ,

 

To my knowledge, no, it's not possible in Power BI. Upon refresh it loads the most up-to-date data.

 

So you may need two tables : Original Table and Updated Table.  And then use functions like LOOKUPVALUE() to identify the modified data.

For more information, you may refer to: How to track changes in the table in Power Query - Exceed

 

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors