Forum Discussion

Analitika's avatar
Analitika
Post Prodigy
6 years ago

Transform data from file

Now im use thsi formula

 

let
Source1 = Sybase.Database(SelectServer, SelectDatabase1),
DBA_view_DidknObj1 = Source1{[Schema="DBA",Item="view_DidknObj"]}[Data],
Source2 = Sybase.Database(SelectServer, SelectDatabase2),
DBA_view_DidknObj2 = Source2{[Schema="DBA",Item="view_DidknObj"]}[Data],

Append = Table.Combine( {DBA_view_DidknObj1, DBA_view_DidknObj2} ),
#"Sorted Rows" = Table.Sort(Append,{{"OPE_ID", Order.Ascending}})
in
#"Sorted Rows"

 

 

But i have about 100 sources wich dinamically  changes, it is possible this part

 

Source1 = Sybase.Database(SelectServer, SelectDatabase1),
DBA_view_DidknObj1 = Source1{[Schema="DBA",Item="view_DidknObj"]}[Data],

 

take from text file which i will generate with other tool, or PBi can do that for me? for example take list of databases from txt file and loop throught them?

 

 

Need Help

4 Replies

    • Analitika's avatar
      Analitika
      Post Prodigy
      6 years ago

      I have text or excel file

       

      IP | Dbname

      100.11.11.23 | db1

      100.10.110.20 | db2

      100.101.111.03 | db3

      100.1.101.11 | db4

       

      i need this file convert to

       

      let
      Source1 = Sybase.Database("100.11.11.23", "db1"),
           DBA_view_DidknObj1 = Source1{[Schema="DBA",Item="view_DidknObj"]}[Data],
      Source2 = Sybase.Database("100.10.110.20", "db2"),
           DBA_view_DidknObj2 = Source2{[Schema="DBA",Item="view_DidknObj"]}[Data],

      Source3 = Sybase.Database("100.101.111.03", "db3"),
           DBA_view_DidknObj3 = Source3{[Schema="DBA",Item="view_DidknObj"]}[Data],

      Source4 = Sybase.Database("100.1.101.11", "db4"),
           DBA_view_DidknObj4 = Source4{[Schema="DBA",Item="view_DidknObj"]}[Data],

       

      Append = Table.Combine( {DBA_view_DidknObj1, DBA_view_DidknObj2, DBA_view_DidknObj3, DBA_view_DidknObj4} ),
      #"Sorted Rows" = Table.Sort(Append,{{"OPE_ID", Order.Ascending}})
      in
      #"Sorted Rows"

       

      now i need  do same thing with 100 sources

      • mahoneypat's avatar
        mahoneypat
        Microsoft Employee
        6 years ago

        This is a good use of a custom function.  Here is how to approach this.

         

        1.  Make a table with your 100 sets of values (IP and database name).  Set both to Text data type.

        2. Make a custom function like this one (make a blank query and paste this M code over the existing code in the Advanced Editor).  Call it something like fxData

        (ipnumber, dbase) =>

        let

        Source = Sybase.Database(ipnumber, dbase),

        DBA_view = Source{[Schema="DBA",Item="view_DidknObj"]}[Data]

        in

        DBA_view

         

        3. On your first table, on Add Column tab click on Invoke Custom Function.  Choose fxData (or whatever you called it), and select the correct two columns as input.

         

        4. At this point you should have a table of 100 tables.  Simply click the expand button on the top of the tables column to expand and combine all the data.

         

        If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

        Regards,

        Pat