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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Bastille92
Frequent Visitor

Multiple Excel Sheets for a Rolling Historical.

Hi all,

 

Relatively new to Power BI, but quite capable in Excel.

 

Lately ive had a pretty challenging task at hand, From my company, im capable of downloading on a weekly basis an excel spreadsheet report of the company's users and their usage.

 

My challenge is this...

 

I need to build a Dashboard to represent the data both in the now, for current information. as well as a way to create a historical presentation via snapshots of multiple excel spreadsheets.

 

all excel spreadsheets are stored in one folder, kept organized by date format such as this.

 

2018-03-24

2018-03-28 etc.

 

Now heres the challenge.

 

I have seen threads on how to merge multiple excel spreadsheets into consolidate database.

but what i need is a rolling history of just 2 years worth of spreadsheet, meaning each week the power BI needs to know how many excel sheets from the folder to pull data from... 

 

and to remove the data from the consolidated sheet that is out of the 2 years range.

 

Any advise would be greatly appreciated.

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

What I would do is use the file name to create a date column then filter on the date column to be within the last two years.

 

For an example I will use a folder that only creates files on the 1st day of every quarter but this will work with any amount of files

 

So I have a folder with the following files

 

2018-01-01.xlsx

2017-10-01.xlsx

2017-07-01.xlsx

2017-04-01.xlsx

2017-01-01.xlsx

2016-10-01.xlsx

2016-07-01.xlsx

2016-04-01.xlsx

2016-01-01.xlsx

2015-10-01.xlsx

2015-07-01.xlsx

2015-04-01.xlsx

2015-01-01.xlsx

 

I would then duplicate the column and transform it such that it becomes a date column. Then filter this date column so the date is either in the last 24 months or in the current month

 

Once you have these files you can then combine the binaries in the column and get the combined data.

I havent done this set as my example doesnt have a binaries column. This does rely on every file having the same internal structure and the file names following a set structure

 

I have put the power query code below so you can see the example. To view this copy and paste the code into a blank query via the advanced editor in the Query Editor.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtNA1MAQivYqc4gqlWB2wmLmuoQGmmIE5FjETLGIY5plhMc8Mi3lmWMwzw2KeKRbzTLGYZ4rFPFMU82IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [FileName = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"FileName", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "FileName", "FileName - Copy"),
    #"Renamed Columns" = Table.RenameColumns(#"Duplicated Column",{{"FileName - Copy", "Date"}}),
    #"Extracted First Characters" = Table.TransformColumns(#"Renamed Columns", {{"Date", each Text.Start(_, 10), type text}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Extracted First Characters",{{"Date", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type1",{{"Date", Order.Ascending}}),
    #"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each Date.IsInPreviousNMonths([#"Date"], 24) or Date.IsInCurrentMonth([#"Date"]))
in
    #"Filtered Rows"

 

I hope this helps.

 

View solution in original post

HI @Anonymous,

 

So you want to know how to get data from excel files(binary type) and combine them, right?
If this is a case, you can refer to below formulas:

 

1. Add column to get data from contents and merge detail tables in each rows:

#"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each Table.Combine(List.Transform(Excel.Workbook([Content])[Data], each Table.PromoteHeaders(_, [PromoteAllScalars=true]))))

7.PNG

2. Transform above column to table.

    Custom1 = Table.Combine(#"Added Custom"[Custom])

8.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

3 REPLIES 3
v-shex-msft
Community Support
Community Support

HI @Bastille92,


Since i'm not so clarify your requirement, can you please share some sample data and expected result?

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

What I would do is use the file name to create a date column then filter on the date column to be within the last two years.

 

For an example I will use a folder that only creates files on the 1st day of every quarter but this will work with any amount of files

 

So I have a folder with the following files

 

2018-01-01.xlsx

2017-10-01.xlsx

2017-07-01.xlsx

2017-04-01.xlsx

2017-01-01.xlsx

2016-10-01.xlsx

2016-07-01.xlsx

2016-04-01.xlsx

2016-01-01.xlsx

2015-10-01.xlsx

2015-07-01.xlsx

2015-04-01.xlsx

2015-01-01.xlsx

 

I would then duplicate the column and transform it such that it becomes a date column. Then filter this date column so the date is either in the last 24 months or in the current month

 

Once you have these files you can then combine the binaries in the column and get the combined data.

I havent done this set as my example doesnt have a binaries column. This does rely on every file having the same internal structure and the file names following a set structure

 

I have put the power query code below so you can see the example. To view this copy and paste the code into a blank query via the advanced editor in the Query Editor.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtNA1MAQivYqc4gqlWB2wmLmuoQGmmIE5FjETLGIY5plhMc8Mi3lmWMwzw2KeKRbzTLGYZ4rFPFMU82IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [FileName = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"FileName", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "FileName", "FileName - Copy"),
    #"Renamed Columns" = Table.RenameColumns(#"Duplicated Column",{{"FileName - Copy", "Date"}}),
    #"Extracted First Characters" = Table.TransformColumns(#"Renamed Columns", {{"Date", each Text.Start(_, 10), type text}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Extracted First Characters",{{"Date", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type1",{{"Date", Order.Ascending}}),
    #"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each Date.IsInPreviousNMonths([#"Date"], 24) or Date.IsInCurrentMonth([#"Date"]))
in
    #"Filtered Rows"

 

I hope this helps.

 

HI @Anonymous,

 

So you want to know how to get data from excel files(binary type) and combine them, right?
If this is a case, you can refer to below formulas:

 

1. Add column to get data from contents and merge detail tables in each rows:

#"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each Table.Combine(List.Transform(Excel.Workbook([Content])[Data], each Table.PromoteHeaders(_, [PromoteAllScalars=true]))))

7.PNG

2. Transform above column to table.

    Custom1 = Table.Combine(#"Added Custom"[Custom])

8.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.