cancel
Showing results for 
Search instead for 
Did you mean: 
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
Super User
Super User

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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors