Dataflow Gen2 - Getting source name

[tcollett0]

Now to Dataflow Gen2 and Power Query:

I have a list of sources (individual databases in Business Central) and from there I want to pull data from each source (which I can do).  Here is a sample list of the sources (shows up as my source):

In the next step (Navigation 1), my data shows up and this is what it is using to get the data.

However, I want to have the source name (Central Kentucky) also in the next applied step in the table and on each column.  When I try to do this, I either get all the names from above.  I would like to get this all the way down into the Navigation 3 step if possible. 

How do I do this programmatically?  Basically I want my source name on each record at the most granular level possible in my data. 

[tcollett0]

Here is the code:

let

  Source = Dynamics365BusinessCentral.ApiContentsWithOptions("CityWide-UAT", null, null, [UseReadOnlyReplica = true, AcceptLanguage = null, Timeout = null, ODataMaxPageSize = null]),

  #"Navigation 1" = Source{[Name = "Central Kentucky"]}[Data],

  #"Navigation 2" = #"Added custom"{[Name = "WebServices"]}[Data],

  #"Navigation 3" = #"Navigation 2"{[Name = "CW_G_L_Entries", Signature = "table"]}[Data]

in

  #"Navigation 3"

I want to get the portion in red and add it as a column (each row will have that value in it).

[v-karpurapud · Accepted solution]

Hi @tcollett0 

To achieve this, define a variable to store the source name string before navigation steps, and then add it as a custom column after loading the target table. Adjust existing code as follows:

let

  Source = Dynamics365BusinessCentral.ApiContentsWithOptions("CityWide-UAT", null, null, [UseReadOnlyReplica = true, AcceptLanguage = null, Timeout = null, ODataMaxPageSize = null]),

  SourceName = "Central Kentucky",

  Navigation1 = try Source{[Name = SourceName]}[Data] otherwise error "Source '" & SourceName & "' not found",

  Navigation2 = try Navigation1{[Name = "WebServices"]}[Data] otherwise error "WebServices not found",

  Navigation3 = try Navigation2{[Name = "CW_G_L_Entries", Signature = "table"]}[Data] otherwise error "CW_G_L_Entries not found",

  AddSourceColumn = Table.AddColumn(Navigation3, "Source_Location", each SourceName, type text)

in

  AddSourceColumn

This ensures that each row in your final result will carry the correct source name without hardcoding it multiple times.

If this post helps, kindly mark it Accepted Solution. It will helps others to find more easily.

Thank You!

[v-karpurapud]

Hi @tcollett0 

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.

Thank you.

[v-karpurapud]

Hi @tcollett0 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-karpurapud]

Hi @tcollett0 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-karpurapud]

Hi @tcollett0 

Welcome to the Microsoft Fabric Forum. Also, thank you @yvaubourg for your quick response.
 

To programmatically add the source name (e.g., "Central Kentucky") as a column to a Dataflow Gen2 query, convert your query into a parameterized function that takes the source name as input. This function should extract data from each Business Central database and add a custom column with the source name for every row. After defining the function, create a table with all your source names (e.g., Boston, Central Kentucky) and use the “Invoke Custom Function” feature to apply your query across all databases. This ensures the source name is embedded in the data at the most granular level and allows consistent reuse of the logic across multiple data sources without hardcoding or duplicating steps. This approach follows best practices in Power Query and Dataflow Gen2 for scalable multi-tenant data extraction.

If this information helps resolve your issue, kindly consider marking this response as the Accepted Solution, as it may assist other community members facing similar challenges.

Thank You!

[yvaubourg]

Hi @tcollett0,

Based on your example, in Navigation 1 the file name is hardecoded. Is that right?

If yes, you can add a derived column.

If no, can you give me more detail on how you will dynamically read the Data for all files?

Regards,

Yohann 

[tcollett0]

Here is what I have.  I am using Dataflow Gen2 so that I can get to the endpoints of a minimum of a dozen databases.  Each of these databases are structurally the same, but they belong to different companies and therefore have to be isolated (this was the design before I got there). 

I go to database "Central Kentucky" and it gives me the list of companies above as the source and then you can see that the next level in Navigation 1 it starts breaking it down into only "Central Kentucky".  As I go through Navigations 2 and 3, I have the records for "Central Kentucky", but I want to add "Central Kentucky" as a new location column.  

My goal was to find a way programmatically that I could copy the query and change the source name only and have the location as an additional column in any layer that I choose by adding a new column.  

Does that make sense?