Hi @Moatasem, here is the detailed list of transformations I do currently. I’m trying to understand how to do all of these transformations in one query by iterating over column indexes:
table 1 renamed as Bad_data_example_1Bob:
let
Source = Excel.Workbook(File.Contents("C:\Users\PATH\TO\FILE\Bad_data_example.xlsx"), null, true), //Generic file path
Bad_data_example_Sheet = Source{[Item="Bad_data_example",Kind="Sheet"]}[Data], //Select sheet
Remove_other = Table.SelectColumns(Bad_data_example_Sheet,{"Column1", "Column2"}),//Remove other columns. Loop to start at this step
Rate_Column = Table.AddColumn(Remove_other, "Rate", each Remove_other[Column2]{1}),//Rate column added from 2nd row = {1}
Name_Column = Table.AddColumn(Rate_Column, "Name", each Remove_other[Column2]{0}),//Name column added from 1st row = {0}
No_null = Table.SelectRows(Name_Column, each ([Column1] <> null)),//Filtered out all null values
Renaming_bob = Table.RenameColumns(No_null,{{"Column1", "Date"}, {"Column2", "Hours"}})//Renaming columns before appending
in
Renaming_bob
Duplicate Bad_data_example_1Bob above and rename: Bad_data_example_2Gary
let
Source = Excel.Workbook(File.Contents("C:\Users\PATH\TO\FILE\Bad_data_example.xlsx"), null, true), //Generic file path
Bad_data_example_Sheet = Source{[Item="Bad_data_example",Kind="Sheet"]}[Data], //Select sheet
Remove_other = Table.SelectColumns(Bad_data_example_Sheet,{"Column1", "Column3"}),//Remove other columns. Loop to start at this step. Not sure how to carry column as variable. Possibly use index and iterate through?
Rate_Column = Table.AddColumn(Remove_other, "Rate", each Remove_other[Column3]{1}),//Rate column added from 2nd row = {1}. All rate values on same row. Similar to above, iterate across column indexes?
Name_Column = Table.AddColumn(Rate_Column, "Name", each Remove_other[Column3]{0}),//Name column added from 1st row = {0}
No_null = Table.SelectRows(Name_Column, each ([Column1] <> null)),//Filtered out all null values
Renaming_Gary = Table.RenameColumns(No_null,{{"Column1", "Date"}, {"Column3", "Hours"}})//Renaming columns before appending. Column 3 as variable?
in
Renaming_Gary
Duplicate Bad_data_example_2Gary above and rename: Bad_data_example_3Rolf:
let
Source = Excel.Workbook(File.Contents("C:\Users\PATH\TO\FILE\Bad_data_example.xlsx"), null, true), //Generic file path
Bad_data_example_Sheet = Source{[Item="Bad_data_example",Kind="Sheet"]}[Data], //Select sheet
Remove_other = Table.SelectColumns(Bad_data_example_Sheet,{"Column1", "Column4"}),//Remove other columns. Loop to start at this step and create Column{x} as variable. How to define variable here? Possibly use index and iterate through?
Rate_Column = Table.AddColumn(Remove_other, "Rate", each Remove_other[Column4]{1}),//Rate column added from 2nd row = {1}. All rate values on same row. Similar to above, iterate across column indexes?
Name_Column = Table.AddColumn(Rate_Column, "Name", each Remove_other[Column4]{0}),//Name column added from 1st row = {0}
No_null = Table.SelectRows(Name_Column, each ([Column1] <> null)),//Filtered out all null values
Renaming_Rolf = Table.RenameColumns(No_null,{{"Column1", "Date"}, {"Column4", "Hours"}})//Renaming columns before appending. Column 4 as variable?
in
Renaming_Rolf
Now Append all three tables and change column types:
let
Source = Table.Combine({Bad_data_example_1Bob, Bad_data_example_2Gary, Bad_data_example_3Rolf}),
Type_Change = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Hours", Int64.Type}, {"Rate", Currency.Type}, {"Name", type text}})
in
Type_Change
