Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
zhivaninz
Helper I
Helper I

Folder queries when not all files have same table structure - brute force vs a better solution?

Data stream A comprises a single Excel file (F) each month, which has three tables in it, I, II, and III.

 

We use a folder query on the folder with these files, by table, to generate the data model.

 

We also have data stream B, which is also a single Excel file (G) each month. Historically, this has only had one table in it per month, I. The I table here (G) is slightly different to the I table from the other file (F).

 

Now, data stream B for various reasons has certain benefits. Ideally, we’d use this stream for everything, however because it hasn’t had the II and III tables we’ve stuck to stream A.

 

Now, they are adding tables II and III to the stream B file, G, but only for future months. These are what I call “new format” G files.

 

What I want to do is create a combined query that uses the stream A files for tables I and II in the past, but then use stream B from

now until the future e.g.:

 

Data

Pre Today

Now and Future

II

Stream A

Stream B

III

Stream A

Stream B

 

At first I thought this would be as simple as creating a new query for the Data Stream B folder that looks up tables II and III. However, the problem is this: the historical stream B files (G) do not have tables II and III. As a result, I get a query error as it is looking for those tables, but they are missing and I get a key error.

 

The one solution I see is to create a NEW folder for the new format G (stream B) files, and only save the new versions in that folder. This would avoid the key error, but would mean three folders:

 

  1. Stream A files (F)
  2. Stream B files (G) – old format
  3. Stream B files (G) – new format

Or, is there a way I can create the queries for tables II and III for the folder in a way that it EXCLUDES all the old files?

2 REPLIES 2
lbendlin
Super User
Super User

what's a data stream?  Do you mean a dataflow ?

No, just stream in the generic sense. One part of the business provides stream A, the other stream B.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors