Why editing in the SampleFileQuery screw the other main query

Hi,
I’ve read that when combining identical files, it is recommended to do any transformation in the example file.
I’ve 3 files identical files as follows:
image
CompanyA.xlsx (10.2 KB)
CompanyB.xlsx (10.2 KB)
CompanyC.xlsx (10.6 KB)
So, I go through the process for combine them all as follows:


image








image
image







So my question is why when I work with the sample file the other query screw and broke?
Shall I work only with the main generated query from the first place?
And if working with the main generated query is the way to go, then when is the case to only work with the sample file?
Thanks

-Later Edit-
I’ve realized that I deleted this thread by mistake and created another one.
I am sorry for that.

Hi @MGadAllah,

Here’s what I did

You need to read that from the inside out.

First, using Excel.Workbook([Content]) we’re returning this table for each file
image

.
From that table we are keeping Rows that match this condition: [Kind] = “Table” and Text.StartsWith( [Name], “Company”)

From the remaining rows in the table we’re extracting a list with tables in the [Data] column
And finally we’re combining those tables with Table.Combine

.

I made a reference to this query and extracted the [Custom] column as a list, to combine them. Table.Combine( Sample[Custom] )

.
And finally unpivoted the columns

image

.
Here’s the sample file.
eDNA - Combine tables.pbix (19.5 KB)

I hope this is helpful

Much appreciated and thanks a lot.
Please, @Melissa … I can realize that I’m missing a solid foundation in Power Query.
So, please, if I want to get a solid foundation, what shall I do?
Thanks

@MGadAllah start by working through the Power Query related courses in the portal

I recommend this order:

  • Data Transformations And Modeling
  • Fundamentals In Power Query And M
  • Depending on if already available a new course on working with nested structured values OR the webinars: Nested Structured Values (Part 1) & Nested Structured Values (Part 2)
  • Applied Problem Solving with Power Query/M

Personally, I’ve learned the most by doing and experimenting. There are many #excelchallenge and/or #powerquerychallenge 's online and I can highly recommend to participate, explore solutions by others and ask queystions - there is a great community out there.
A final recommendation follow ExcelBI on LinkedIn, Vijay posts daily challenges for you to get stuck in. Best of luck with your learnings!

1 Like

Thanks a lot @Melissa
I will follow your words to the letter.
Really appreciated your valuable time.

Hello @MGadAllah

Did the response from @Melissa help solve your query?

If not, can you let us know where you’re stuck and what additional assistance you need?

If it did, please mark her answer as the SOLUTION.

Thank you

1 Like

Thanks a lot my friend.

1 Like