Forum Discussion

T-Pan's avatar
T-Pan
Icon for Helper I rankHelper I
2 years ago
Solved

Get data from folder issue.

Hello everyone, on my job i have to create a report which will be updated daily with the excels in picture below. As you will see, all columns of these excels have the same Column names except the se...
Need Help
  • T-Pan's avatar
    T-Pan
    2 years ago

    spinfuzer m_dekorte , in tranform sample query, i deleted the promote to headers step and add another step of "delete the first row". So now i have column 1, column 2, etc,  as headers and in my appended query  renamed the columns as i want. I don't know if its the right way but it works.

m_dekorte's avatar
m_dekorte
Icon for Resident Rockstar rankResident Rockstar
2 years ago

Alternatively you can transform column names, that will look something like this:

Table.TransformColumnNames( PrevStepName, each if Text.Contains(_, ".") then Text.AfterDelimiter(_, " ") else _)

 

And if as you say it is always the second column, you can easily extract that date and add its value to a new column in the table. That will look something like this.

Table.AddColumn( PrevStepName, "Date", each Text.BeforeDelimiter( Table.ColumnNames(PrevStepName){1}, " ")) 

  • T-Pan's avatar
    T-Pan
    Icon for Helper I rankHelper I
    2 years ago

    I do this after i have combined the excels in power query?(get data from folder / combine and tranform) 

    • m_dekorte's avatar
      m_dekorte
      Icon for Resident Rockstar rankResident Rockstar
      2 years ago

      No, you would add that to the Transform Sample Query. If you share the code that's in that Transform Sample Query, we can help incorporate that rename step and optionally the add column step as well if desired.

      • T-Pan's avatar
        T-Pan
        Icon for Helper I rankHelper I
        2 years ago

        Tomorrow morning will ne the first thing that i will do when i arrived to the office, be sure about that. 😛