Latest Enterprise DNA Initiatives

Iterate over each column and add new

I have a table with cells stacked above the hours column. Here is my current data:

            Bob GaryRolf
            55  20  30

01/01/2020	20	22	44
08/01/2020	30	10	20
15/01/2020	22	24	30

Is it possible to iterate over each column, add new column based on cells above and then append? I’m trying not to repeat transformations for each column. See below for output I would like.

Date	   Hours Rate	Name
01/01/2020	20	55.00	Bob
08/01/2020	30	55.00	Bob
15/01/2020	22	55.00	Bob
01/01/2020	22	20.00	Gary
08/01/2020	10	20.00	Gary
15/01/2020	24	20.00	Gary
01/01/2020	44	30.00	Rolf
08/01/2020	20	30.00	Rolf
15/01/2020	30	30.00	Rolf

Sample data attached here: Bad_data_example.xlsx (16.6 KB)
Desired output Transformed_Ok is on next sheet

Below is a sample query of Column 2 for Bob:

let
  Source = Excel.Workbook(File.Contents("\\Path\To\File\Bad_data_example.xlsx"), null, true),
  Bad_data_example_Sheet = Source{[Item="Bad_data_example",Kind="Sheet"]}[Data],
  #"Added_rate" = Table.AddColumn(Bad_data_example_Sheet, "Rate", each 
  #"Bad_data_example_Sheet"[Column2]{1}),
  #"Added_name" = Table.AddColumn(Added_rate, "Name", each #"Added_rate"[Column2]{2})
in
  #"Added_name"`

Where [Column2]{1} is the cell reference for the Column 2, Row 1 and [Column2]{2} is reference for Column 2, Row 2.

Update: I read this article for do-loop and next-loop but not certain how I can apply either of these here.

Hi @izzleee,

Welcome to the Forum.

Provide your sample in XLSX so members can accurately determine the data format you are dealing with and what you are trying to achieve.

Thanks Melissa, I’ve added the attachment and included minor formatting updates.

Bad_data_example.xlsx (25.8 KB)
the solution consists of three queries, two are not loaded to the worksheet, and only one is loaded with the transformed data

1 Like

I’ll inlcude whole transform shortly. I didn’t include earlier but should have. Thanks Moatasem

Hi @izzlee, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.
  • When posting a topic with formula make sure that it is correctly formatted to preformatted text </>.
    image
  • Use the proper category that best describes your topic
  • Provide as much context to a question as possible.
  • Include the masked demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline How To Use The Enterprise DNA Support Forum. Not adhering to it may sometimes cause delay in getting an answer.

Please also check the How To Mask Sensitive Data thread for some tips on how to mask your pbix file.

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

Hi @izzleee,

Have you explored the solution provided by @Moatasem?
If you haven’t please do so and get back with your findings OR mark that post as Solution

Thanks!

Hi Melissa,

@Moatasem didn’t provide a solution but commented on missing info. Can you please help me edit my original question? I want to add the whole transformation. This will help users in future if I can summarise more clearly.

Hi @izzleee,

I’m happy to help of course but I don’t quite follow… I’ve looked at the solution posted by @Moatasem and this is the generated output. Which matches your initial request. So what seems to be the issue?

image

Hi @izzlee, we’ve noticed that no response has been received from you since the 18th of December. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

Hi @izzleee , a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

Hi @EnterpriseDNA/@Moatasem, I don’t understand the solution. It looks like the transforms have been reworked on an excel spreadsheet. Is this right?
I’m hoping for a solution to iterate over columns in the advanced editor. Can you send a solution in M code?

Hi @izzleee,

Open the XLSX file posted by @Moatasem , select the second tab (1) and a cell in the “green” table (2)

Go to Query (3) and Edit (4)

You’ll see the Transformed_data result query contains a Merge step
When you select that you see it references another query

Select that query to see how it’s built

I hope this is helpful.