Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors