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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors