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

From Folder, filter by specific TABs, transform TABs from each workbook differently.

Good Afternoon,

 

I have an interesting scenario and wondering how others would solve it, I am currently looking at the route of doing each file separately but I am sure there is a better way.

 

Scenario is;

Multiple Excel source files with multiple TABs but only certain one's are needed, call them currency transaction TABs labelled as (EUR, USD, GBP etc.)

 

I am able to filter the source to those specific items from another table.

 

The TAB's in each workbook are slightl;y different, the date and reference column are swopped in one file casuing errors when the files are loaded together.

 

Ideally I'd like to

A) Get the data from one TAB and make some transformations, 'promote headers'; 'align columns' etc, apply that process to all the sel;ected TAB's in that workbook.

 

B) Do the same for the next workbook/s.

 

C) load the files together as per the usual get data from folder function.

 

D) Make any transformations I need to make on the whole dataset

 

 

Am I going to be stuck in doing each workbook individually?

 

Thanks for reading

 

Martin

 

3 REPLIES 3
smpa01
Super User
Super User

Hello @NumeritasMartin,

It is always difficult to answer without a sample data. However, I like to think that it can be achieved what you have in mind.

 I have assumed that you are looking for a batch process (for a cluster of transformation) per file. It can be done by writing s custom code and running that code on each file. For example, I have created a sample dataet as following

Capture.JPG

 and I want to run a following transformation

a) Unselect any tabs that are not USD/GBP - File level transformation

b) From each table pick up only the colums => Name/Value only - Tab level transformation

c) Promoted Headers - Tab level transformation

d) Finally produce an appened result from all the trsnformed tables from each of the tab with the name of the Tab in a separate column

Capture.JPG

(P as text)=>let
    Source = Excel.Workbook(File.Contents(P), null, true),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Name] <> "Non-Currency")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Name", "Data"}),
    #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each let
   Source=[Data],
   #"Transposed Table" = Table.Transpose(Source),
    #"Sorted Rows" = Table.Sort(#"Transposed Table",{{"Column1", Order.Ascending}}),
    #"Filtered Rows1" = Table.SelectRows(#"Sorted Rows", each ([Column1] = "Value") or ([Column1] = "Name")),
    #"Transposed Table1" = Table.Transpose(#"Filtered Rows1"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true])
in
    #"Promoted Headers"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Data"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Name", "Value"}, {"Name.1", "Value"})
in
    #"Expanded Custom"

When you invoke this function please input the value of P as the path of the file. e.g. "C:\Users\Desktop\Test1\X.xlsx" but without the quoted marks on each end as this - C:\Users\Desktop\Test1\X.xlsx

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Thank you for the quick reply @smpa01 , your solution looks like it should work for me, I am trying to put it into application but I'm running into a little problem because I don't quite understand what steps happen when, the issue I am having is basically I have a top row to remove before the transposition step, but when I then try to filter the rows on my expected results I'm getting an error of value not found.

 

Transform PIC.PNG

 

I put a filter in so that I can pull the TAB names from a table which works fine, I plan to do the same with the 'Columns' too.

 

Thanks for your help

 

Martin

@NumeritasMartin  sory could not have come back to you earlier

 

Can you try this

(P as text)=>let
    //Connecting to file
    Source = Excel.Workbook(File.Contents(P), null, true),
    //Remove Unnecessary tabs, known tabs are known to the analyst
    #"Filtered Rows" = Table.SelectRows(Source, each ([Name] <> "Non-Currency")),
    //Steps to initiate transormation steps in sequence in each table
    #"Added Custom1" = Table.AddColumn(#"Filtered Rows", "Custom", each let
   Source = [Data],
   // Remove Random row that resides at row#1 from each table
   #"Removed Top Rows" = Table.Skip(Source,1),
   // Transpose Table
    #"Transposed Table" = Table.Transpose(#"Removed Top Rows"),
    #"Sorted Rows" = Table.Sort(#"Transposed Table",{{"Column1", Order.Ascending}}),
    #"Filtered Rows1" = Table.SelectRows(#"Sorted Rows", each ([Column1] = "Value") or ([Column1] = "Name")),
    #"Transposed Table1" = Table.Transpose(#"Filtered Rows1"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Name", type text}, {"Value", Int64.Type}})
in
    #"Changed Type"),
    #"Removed Other Columns1" = Table.SelectColumns(#"Added Custom1",{"Custom"}),
    #"Expanded Custom1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Custom", {"Name", "Value"}, {"Name", "Value"})
in
    #"Expanded Custom1"
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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.