Initial table formatting for multiple tables in Power BI simultaneously?

Question

In Power BI I imported multiple same type tables (≈ 50 tables from different sources). For example 10 tables from one source, other 10 tables from another source. So on..

---

For each of tables (Ast10, Ast11, Ast12....and so on) I need to do same initial formatting:

• Remove blank rows
• Promote first row to headers
• Select only 8 specific columns in each table

All these tables are the same in type: same columns, same blank rows at the top, same columns to select, but different data inside.

---

When I select multiple tables (using Shift or Ctrl) at a time and start formatting:

Only one table affected.

---

How can I simply do first primitive formatting of all multiple tables in Power BI? So each table removed blank rows, firt rows promoted to headers, only specific columns selected in each table? Is there a way to do it without 50 times repetition.

Answer 1

Create a custom function and store the logic there. e.g. for the following table:

Create a new query called myFunction and paste the following:

(t as table) =>

    let
         #"Removed Blank Rows" = Table.SelectRows(t, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
        #"Promoted Headers" = Table.PromoteHeaders(#"Removed Blank Rows", [PromoteAllScalars=true]),
        #"Removed Other Columns" = Table.SelectColumns(#"Promoted Headers",{"Header 1", "Header 2"})
    in
        #"Removed Other Columns"

And then call it from your other query:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}),
        Custom1 = myFunction( #"Changed Type")
    in
        Custom1