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

How to replicate all the Applied Steps from one base to another?

Hi guys!

I have 6 clients, where each of them have 3 similar databases, in which I will need to merge the tables.

However, my question is... What I will do in one base, which is get the data through folders, combine the tables and do all the Applied Steps, I will do the same for all the other 6, because I need the base of the 6 clients in a report.

Does anyone know how I can replicate everything I did in 1 client, to the others, without having to do the same work again of everything I am going to do in the Applied Steps?

1 ACCEPTED SOLUTION

HJi @Matheuspeppers,

 

you can of course duplicate the queries and modify input individually, but this would mean a bit of headache if you ever come to modify the steps.

 

I would go with something like this, if you feel comfortable.

 

Let's assume that you have a query like this:

let
    Source = Excel.Workbook(File.Contents("\\ClientFolder#1\Demo PQ.xlsx"), null, true),
    Table = Source{[Item="P_L",Kind="Table"]}[Data],
    #"Kept First Rows" = Table.FirstN(Table,15),
    #"Removed Other Columns" = Table.SelectColumns(#"Kept First Rows",{"Account", "31/03/2022", "31/03/2021", "31/03/2020"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([#"31/03/2022"] <> 0))
in
    #"Filtered Rows"

 

Assuming that I have another 2 similar files, which should be processed completely identical, I can convert it into something like this:

let 
    Source = {
            "\\ClientFolder#1\Demo PQ.xlsx",
            "\\ClientFolder#2\Demo PQ.xlsx",
            "\\ClientFolder#3\Demo PQ.xlsx"
    },

    f = (path as text)=>
        let
            Source = Excel.Workbook(File.Contents(path), null, true),
            Table = Source{[Item="P_L",Kind="Table"]}[Data],
            #"Kept First Rows" = Table.FirstN(Table,15),
            #"Removed Other Columns" = Table.SelectColumns(#"Kept First Rows",{"Account", "31/03/2022", "31/03/2021", "31/03/2020"}),
            #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([#"31/03/2022"] <> 0))
        in
            #"Filtered Rows",
    
    process = List.Accumulate(Source, {}, (a,n)=> a & {f(n)}),
    output = Table.Combine (process)
in output

 

Now the exact same steps applied to all three files. If the files require different steps to bring them to a uniformed look before applying common steps, it can be transformed as following:

let 
    Source = {
            Query1,
            Query2,
            Query3
    },

    f = (t as table)=>
        let
            #"Removed Other Columns" = Table.SelectColumns(t,{"Account", "31/03/2022", "31/03/2021", "31/03/2020"}),
            #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([#"31/03/2022"] <> 0))
        in
            #"Filtered Rows",
    
    process = List.Accumulate(Source, {}, (a,n)=> a & {f(n)}),
    output = Table.Combine (process)
in output

In this case, I assumed that we need to keep different number of rows in each file (Query1,2,3 are responsible for this), but the rest of the steps are the same for all three files.

 

Kind regards,

John

View solution in original post

2 REPLIES 2
Imrans123
Advocate V
Advocate V

In Power query, with a query open, you can go to advanced editor and it will show you the text form of all the steps that you have applied to that query. You can copy paste those steps into a new query without having to redo all the work.

HJi @Matheuspeppers,

 

you can of course duplicate the queries and modify input individually, but this would mean a bit of headache if you ever come to modify the steps.

 

I would go with something like this, if you feel comfortable.

 

Let's assume that you have a query like this:

let
    Source = Excel.Workbook(File.Contents("\\ClientFolder#1\Demo PQ.xlsx"), null, true),
    Table = Source{[Item="P_L",Kind="Table"]}[Data],
    #"Kept First Rows" = Table.FirstN(Table,15),
    #"Removed Other Columns" = Table.SelectColumns(#"Kept First Rows",{"Account", "31/03/2022", "31/03/2021", "31/03/2020"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([#"31/03/2022"] <> 0))
in
    #"Filtered Rows"

 

Assuming that I have another 2 similar files, which should be processed completely identical, I can convert it into something like this:

let 
    Source = {
            "\\ClientFolder#1\Demo PQ.xlsx",
            "\\ClientFolder#2\Demo PQ.xlsx",
            "\\ClientFolder#3\Demo PQ.xlsx"
    },

    f = (path as text)=>
        let
            Source = Excel.Workbook(File.Contents(path), null, true),
            Table = Source{[Item="P_L",Kind="Table"]}[Data],
            #"Kept First Rows" = Table.FirstN(Table,15),
            #"Removed Other Columns" = Table.SelectColumns(#"Kept First Rows",{"Account", "31/03/2022", "31/03/2021", "31/03/2020"}),
            #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([#"31/03/2022"] <> 0))
        in
            #"Filtered Rows",
    
    process = List.Accumulate(Source, {}, (a,n)=> a & {f(n)}),
    output = Table.Combine (process)
in output

 

Now the exact same steps applied to all three files. If the files require different steps to bring them to a uniformed look before applying common steps, it can be transformed as following:

let 
    Source = {
            Query1,
            Query2,
            Query3
    },

    f = (t as table)=>
        let
            #"Removed Other Columns" = Table.SelectColumns(t,{"Account", "31/03/2022", "31/03/2021", "31/03/2020"}),
            #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([#"31/03/2022"] <> 0))
        in
            #"Filtered Rows",
    
    process = List.Accumulate(Source, {}, (a,n)=> a & {f(n)}),
    output = Table.Combine (process)
in output

In this case, I assumed that we need to keep different number of rows in each file (Query1,2,3 are responsible for this), but the rest of the steps are the same for all three files.

 

Kind regards,

John

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