Forum Discussion
Transform data from file
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
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