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
BBL
Frequent Visitor

Load multiple tables in Power Query with loop

Hello everyone,

I'm new to this community so I hope that I got the location right..

New in PowerBI, despite a few hours of research I can not find the solution.

I want to load multiple tables from my database with Power Query.

Situation :

Every end of the day, my table TAB which contains the daily productions data is archived with the name TAB_YYYY_MM, with YYYY curent year and MM curent month.
All the data of the month MM are stored in this table, for example for April 2019, the archive will be TAB_2019_04, for June-2018 => TAB_2018_06

 

Capture.JPG

 

With this concept, can someone help me how to load the rolling 12 months tables of history from curent date, in a dynamic way of course.
For today for example, it will be tables TAB_2018_04, TAB_2018_05, ..., TAB_2019_04 and TAB

 

Thanks for your help.

1 ACCEPTED SOLUTION
vmakhija
Post Prodigy
Post Prodigy

@BBL 
You can use the concept of "Invoking Custom Functions" to achieve this.

I tried using Excel files and it works fine. You can use the similar approach with your database.

 

Create custom function as -

(YearMonth as text) =>
let
Source = Excel.Workbook(File.Contents("C:\Users\VMakhij\" & YearMonth & ".xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Category", type text}, {"YearMonth", Int64.Type}, {"Sales", Int64.Type}})
in
#"Changed Type"

 

 

Next, create your table which will be using the above function -

let
StartDate= #date(2018,01,01), 
EndDate = DateTime.LocalNow(), 
DateList = List.Dates(StartDate, Number.From(EndDate)- Number.From(StartDate)+1 ,#duration(1,0,0,0)),
#"Sorted Items" = List.Sort(DateList,Order.Ascending),
#"Converted to Table" = Table.FromList(#"Sorted Items", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "YearMonth", each Number.ToText(Date.Year([Date])) &
Text.PadStart(Number.ToText(Date.Month([Date])),2,"0")),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"YearMonth", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Date"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Columns"),
YearMonth = #"Removed Duplicates"[YearMonth],
#"Sorted Items1" = List.Sort(YearMonth,Order.Descending),
#"Converted to Table1" = Table.FromList(#"Sorted Items1", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns1" = Table.RenameColumns(#"Converted to Table1",{{"Column1", "YearMonth"}}),
#"Added Index" = Table.AddIndexColumn(#"Renamed Columns1", "Index", 1, 1),
#"Filtered Rows" = Table.SelectRows(#"Added Index", each [Index] < 13),
#"Changed Type2" = Table.TransformColumnTypes(#"Filtered Rows",{{"YearMonth", type text}}),
#"Invoked Custom Function" = Table.AddColumn(#"Changed Type2", "DataColumn", each Sheet1([YearMonth])),
#"Expanded DataColumn" = Table.ExpandTableColumn(#"Invoked Custom Function", "DataColumn", {"Category", "YearMonth", "Sales"}, {"Category", "YearMonth.1", "Sales"})
in
#"Expanded DataColumn"

 

 

I wanted to share the PBIX file with you, but don't have a way to upload it from my corporate network.

 

Regards

Vicky

View solution in original post

2 REPLIES 2
BBL
Frequent Visitor

up

vmakhija
Post Prodigy
Post Prodigy

@BBL 
You can use the concept of "Invoking Custom Functions" to achieve this.

I tried using Excel files and it works fine. You can use the similar approach with your database.

 

Create custom function as -

(YearMonth as text) =>
let
Source = Excel.Workbook(File.Contents("C:\Users\VMakhij\" & YearMonth & ".xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Category", type text}, {"YearMonth", Int64.Type}, {"Sales", Int64.Type}})
in
#"Changed Type"

 

 

Next, create your table which will be using the above function -

let
StartDate= #date(2018,01,01), 
EndDate = DateTime.LocalNow(), 
DateList = List.Dates(StartDate, Number.From(EndDate)- Number.From(StartDate)+1 ,#duration(1,0,0,0)),
#"Sorted Items" = List.Sort(DateList,Order.Ascending),
#"Converted to Table" = Table.FromList(#"Sorted Items", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "YearMonth", each Number.ToText(Date.Year([Date])) &
Text.PadStart(Number.ToText(Date.Month([Date])),2,"0")),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"YearMonth", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Date"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Columns"),
YearMonth = #"Removed Duplicates"[YearMonth],
#"Sorted Items1" = List.Sort(YearMonth,Order.Descending),
#"Converted to Table1" = Table.FromList(#"Sorted Items1", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns1" = Table.RenameColumns(#"Converted to Table1",{{"Column1", "YearMonth"}}),
#"Added Index" = Table.AddIndexColumn(#"Renamed Columns1", "Index", 1, 1),
#"Filtered Rows" = Table.SelectRows(#"Added Index", each [Index] < 13),
#"Changed Type2" = Table.TransformColumnTypes(#"Filtered Rows",{{"YearMonth", type text}}),
#"Invoked Custom Function" = Table.AddColumn(#"Changed Type2", "DataColumn", each Sheet1([YearMonth])),
#"Expanded DataColumn" = Table.ExpandTableColumn(#"Invoked Custom Function", "DataColumn", {"Category", "YearMonth", "Sales"}, {"Category", "YearMonth.1", "Sales"})
in
#"Expanded DataColumn"

 

 

I wanted to share the PBIX file with you, but don't have a way to upload it from my corporate network.

 

Regards

Vicky

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.