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
Anonymous
Not applicable

Use same query for multiple data sources (same format), but ONE file at time

Hi folks,

 

sorry if I'm posting in wrong place, this is my first topic here.

 

I have aroud 60 Excel files in a SharePoint folder, with same format, but different values (one from each customer). I need to add them into a big table, and identify each line from the customer name (that is inside the filename as well).

 

This is easy, simply adding the "SharePoint folder" as a source, and them make the magic, right? NO, it will nor work, and I will explain why:

 

FIle is something like that:

 

Company NameIBMMicrosoft
Areas  
Area1200400
Area2300100
Area3100500
Area42090

 

- During the transformation from a single file, I make many transposes and unpivot columns, to use a data written as a "column name". So, the result above will be like this:

 

Company NameAreasValue
IBMArea1200
IBMArea2300
IBMArea3100
IBMArea420
MicrosoftArea1400
MicrosoftArea2100
MicrosoftArea3500
MicrosoftArea490

 

- So, merging every file into a single table, I will not be able to unpivot the values like that, so I need to do it on each file.

 

I ended up creating 60 queries, one for each file, to make all those transposes, unpivots, and giving a column name with the customer (like, Brazil has IBM and Microsoft, Italy has IBM and Telefonica, Japan has Microsoft and BT...). Then, I merge all these files into a single big table to start the visuals.

 

All Excel files are really small (less than 300Kb; however, dealing with 60 queries it's becoming a pain, PowerBI response started to became REALLY slow.

 

So, is there any "smarter" way to deal with that? Looking at the queries, they are all the same, only thing that changes is the filename, and the custom column name that I add to identify which file is. Is there any way to use these values as a parameter for a single query?

 

Here's the query that I'm using. Only thing that changes from one query to another is in blue bold.

 

let
Source = SharePoint.Files("https://site/temp2", [ApiVersion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "FILENAME.xlsx")),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Content"}),
#"Filtered Hidden Files1" = Table.SelectRows(#"Removed Other Columns", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from Query1", each #"Transform File from Query1"([Content])),
#"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1", {"Transform File from Query1"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from Query1", Table.ColumnNames(#"Transform File from Query1"(#"Sample File (2)"))),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Table Column1",{"Column1", "Column5"}),
#"Removed Top Rows" = Table.Skip(#"Removed Columns",2),
#"Removed Alternate Rows" = Table.AlternateRows(#"Removed Top Rows",1,1,999999),
#"Filled Down" = Table.FillDown(#"Removed Alternate Rows",{"Column2"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Filled Down", [PromoteAllScalars=true]),
#"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"Column1", "Block"}, {"Column2", "Code"}, {"Subteam Name", "Service"}}),
#"Removed Top Rows1" = Table.Skip(#"Renamed Columns",1),
#"Filtered Rows1" = Table.SelectRows(#"Removed Top Rows1", each [Code] <> null and [Code] <> ""),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filtered Rows1", {"Block", "Code", "Service"}, "Attribute", "Value"),
#"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Customer", each "Italy"),
#"Renamed Columns1" = Table.RenameColumns(#"Added Custom",{{"Value", "FTE"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns1",{{"FTE", type number}})
in
#"Changed Type"

 

Appreciate any help on that! 🙂

 

[]´s

Paulo

1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

Hi @Anonymous 

I use Folder connector as a test,

when connect to a folder, i select 'edit" instead of "combine and edit",

12.png

Then write the code 

let
    Source = Folder.Files("C:\Users\maggiel\Desktop\case\4\4.15\1"),
    #"C:\Users\maggiel\Desktop\case\4\4 15\1\_1 xlsx" = Source{[#"Folder Path"="C:\Users\maggiel\Desktop\case\4\4.15\1\",Name="1.xlsx"]}[Content],
    #"Imported Excel" = Excel.Workbook(#"C:\Users\maggiel\Desktop\case\4\4 15\1\_1 xlsx"),
    Sheet1_Sheet = #"Imported Excel"{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Company Name", type text}, {"IBM", Int64.Type}, {"Microsoft", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Company Name"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "company"}, {"Company Name", "area"}}),
    #"C:\Users\maggiel\Desktop\case\4\4 15\1\_2 xlsx" = Source{[#"Folder Path"="C:\Users\maggiel\Desktop\case\4\4.15\1\",Name="2.xlsx"]}[Content],
    #"Imported Excel2" = Excel.Workbook(#"C:\Users\maggiel\Desktop\case\4\4 15\1\_2 xlsx"),
    Sheet1_Sheet2 = #"Imported Excel2"{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers2" = Table.PromoteHeaders(Sheet1_Sheet2, [PromoteAllScalars=true]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers2",{{"Company Name", type text}, {"IBM2", Int64.Type}, {"Microsoft2", Int64.Type}}),
    #"Unpivoted Columns2" = Table.UnpivotOtherColumns(#"Changed Type2", {"Company Name"}, "Attribute", "Value"),
    #"Renamed Columns2" = Table.RenameColumns(#"Unpivoted Columns2",{{"Attribute", "company"}, {"Company Name", "area"}}),
    #"Appended Query" = Table.Combine({#"Renamed Columns", #"Renamed Columns2"})
in
    #"Appended Query"

13.png

 

Best Regards

Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
v-juanli-msft
Community Support
Community Support

Hi @Anonymous 

I use Folder connector as a test,

when connect to a folder, i select 'edit" instead of "combine and edit",

12.png

Then write the code 

let
    Source = Folder.Files("C:\Users\maggiel\Desktop\case\4\4.15\1"),
    #"C:\Users\maggiel\Desktop\case\4\4 15\1\_1 xlsx" = Source{[#"Folder Path"="C:\Users\maggiel\Desktop\case\4\4.15\1\",Name="1.xlsx"]}[Content],
    #"Imported Excel" = Excel.Workbook(#"C:\Users\maggiel\Desktop\case\4\4 15\1\_1 xlsx"),
    Sheet1_Sheet = #"Imported Excel"{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Company Name", type text}, {"IBM", Int64.Type}, {"Microsoft", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Company Name"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "company"}, {"Company Name", "area"}}),
    #"C:\Users\maggiel\Desktop\case\4\4 15\1\_2 xlsx" = Source{[#"Folder Path"="C:\Users\maggiel\Desktop\case\4\4.15\1\",Name="2.xlsx"]}[Content],
    #"Imported Excel2" = Excel.Workbook(#"C:\Users\maggiel\Desktop\case\4\4 15\1\_2 xlsx"),
    Sheet1_Sheet2 = #"Imported Excel2"{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers2" = Table.PromoteHeaders(Sheet1_Sheet2, [PromoteAllScalars=true]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers2",{{"Company Name", type text}, {"IBM2", Int64.Type}, {"Microsoft2", Int64.Type}}),
    #"Unpivoted Columns2" = Table.UnpivotOtherColumns(#"Changed Type2", {"Company Name"}, "Attribute", "Value"),
    #"Renamed Columns2" = Table.RenameColumns(#"Unpivoted Columns2",{{"Attribute", "company"}, {"Company Name", "area"}}),
    #"Appended Query" = Table.Combine({#"Renamed Columns", #"Renamed Columns2"})
in
    #"Appended Query"

13.png

 

Best Regards

Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the 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.

Top Solution Authors
Top Kudoed Authors