Forum Discussion

tcollett0's avatar
tcollett0
Frequent Visitor
1 year ago
Solved

Dataflow Gen2 - Getting source name

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 l...
Data Engineering
Lakehouse
  • v-karpurapud's avatar
    v-karpurapud
    1 year ago

    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!

yvaubourg's avatar
yvaubourg
Resolver I
1 year ago

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's avatar
tcollett0
Frequent Visitor
1 year ago

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?