Forum Discussion
Dataflow Gen2 - Getting source name
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!
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!