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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
JediMole
Helper I
Helper I

Finding the common item in 'similar' data sources

I'm creating reports from 7 different client portals, each in their own standalone instance in azure.  A large part of it is event type data, hotels, attendance stats, venue locations and some budgetary items.  I wanted to create a venue report to show how attendance was, and which location seemed to favor hosting attendees, meaning who was used most often and the attendees invited compared to those that actually attended.  Normally the common item would be the Event ID, but to my dismay, the event id is a sequential number that starts the same sequence in each portal.  Meaning there's a good chance the numbers will be the same at some point in each portal export.

 

So my question is, how can I bring all the files into one report and still be able to use an Event ID count as a common column.  Can I replace the 'event id' with a sequential count because I don't need to drill into the event specifics and achieve the result I'm looking for?

 

  1. Bring all the files into power BI as a folder
  2. Transform the data and kill the event id column and replace it with a count column

Recreate the report below with the combined data:

 

JediMole_0-1730997610066.png

 

1 ACCEPTED SOLUTION

Well, @JediMole 
I am not sure what will be your source, but I recommend Sharepoint Folder if you have flat files.

Then when you append them all together, can look like this:

vojtechsima_0-1730999868882.png

This then the measure you will use, it's not a column.

vojtechsima_1-1730999925171.png

vojtechsima_3-1730999980281.png

 

View solution in original post

4 REPLIES 4
vojtechsima
Resident Rockstar
Resident Rockstar

Hello, @JediMole ,
if you don't need event specifics, I would just put all all events together, you can ignore the Event ID and for the the Count of Events, just use regular:

 

COUNTROWS('EventTable')

 

Because you will know, that one event = one row.

Is my idea of pulling the files in from a single folder sound?  I tried that once and it worked as it appended the file name for each different file in the first column.

 

Can you show me how that countrows looks?  is it just a new column or?

Well, @JediMole 
I am not sure what will be your source, but I recommend Sharepoint Folder if you have flat files.

Then when you append them all together, can look like this:

vojtechsima_0-1730999868882.png

This then the measure you will use, it's not a column.

vojtechsima_1-1730999925171.png

vojtechsima_3-1730999980281.png

 

Ohhh I see that now, interesting

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.