Can we add another dataset in Advance Editor window

[mahesh_marathe]

My underlying query from Advance query editor is 

let
    Source = VSTS.Views("ABC", "PQR", null, []),
    #"Work Items - Today1" = Source{[Name="Work Items - Today"]}[Data]

in
    #"Work Items - Today1"

I want to add one more set here, account name is same ABC but vsts project name is XYZ can we do like this so that I can merge two data set. I do not want to create seperate table and then merge because in VSTS I have many projects and I do not want to create table for each project and then merge

let
    Source = VSTS.Views("ABC", "PQR", null, []),
    #"Work Items - Today1" = Source{[Name="Work Items - Today"]}[Data]
    &
    Source = VSTS.Views("ABC", "XYZ", null, []),
    #"Work Items - Today2" = Source{[Name="Work Items - Today"]}[Data]

in
    #"Work Items - Today1" & #"Work Items - Today2"

Please suggest 

[MFelix]

Hi ,

You can do that but you need to have the 2 data source in saparate rows with different names and then.do.the merge picking up your code would.be something like this:

let
Source = VSTS.Views("ABC", "PQR", null, []),
#"Work Items - Today1" = Source{[Name="Work Items - Today"]}[Data],
Source_2 = VSTS.Views("ABC", "XYZ", null, []),
#"Work Items - Today2" = Source{[Name="Work Items - Today"]}[Data],
#'merge 2 sources' = Mergetable ("Work Items -Today1" work item, "Work Items - Today2", workitem, left inner join)
in
#"merge 2 sources"

The merge sintax is incorrect but.you can do it easily by merging the query with it self and then on advance editor changing to the 2 datasources names. Just be sure to merge the queries or append in the last transformation step of each source.

Sorry for not giving a better example but not on my computer.

Regards,
MFelix

[mahesh_marathe]

Yes, I can use merge or append to one dataset by creating seperate datasets for each project, I have many projects in VSTS so I need to create that number of tables and then merge, I was thinking to merge without creating any seperate data set

[MFelix]

Hi @mahesh_marathe,

You don't need to create those tables you only create one single table that as several sources (VSTS) you can do this in 3 ways:

1 - If it's an SQL do the append/merge of the tables in SQL and only return the outcome to Power BI

2 - Do a single query in PBI that as several sources and returns the final result

3 - Do a function in PBI (getting the information and treatment for 1 of the VSTS) and then do a list with the names of the full VSTS and run the function against tha list, this implies that the names of your VSTS must be somehow similar or you have them in a list. (check this site for a more detailed and example how this works - https://www.mattmasson.com/2014/11/iterating-over-multiple-pages-of-web-data-using-power-query/)

OPTION 2:

let
    Source = Excel.Workbook(File.Contents("C:\Desktop\VSTS_Datasource.xlsx"), null, true),
    VSTS1_Table = Source{[Item="VSTS1",Kind="Table"]}[Data],
    VSTS2_Table = Source{[Item="VSTS2",Kind="Table"]}[Data],
    VSTS3_Table = Source{[Item="VSTS3",Kind="Table"]}[Data],
    VSTS4_Table = Source{[Item="VSTS4",Kind="Table"]}[Data],
    VSTS5_Table = Source{[Item="VSTS5",Kind="Table"]}[Data],
    VSTS6_Table = Source{[Item="VSTS6",Kind="Table"]}[Data],
    VSTS7_Table = Source{[Item="VSTS7",Kind="Table"]}[Data],
    VSTS8_Table = Source{[Item="VSTS8",Kind="Table"]}[Data],
    Append_Data = Table.Combine({VSTS1_Table, VSTS2_Table, VSTS3_Table, VSTS4_Table, VSTS5_Table, VSTS6_Table, VSTS7_Table, VSTS8_Table}),
    Format = Table.TransformColumnTypes(Append_Data,{{"Account", type text}, {"Project", type text}})

in
   Format

OPTION 3

Function:

(VSTS_Source) =>

let
    Source = Excel.Workbook(File.Contents("C:\Desktop\VSTS_Datasource.xlsx"), null, true),
    VSTS1_Table = Source{[Item="VSTS"&Number.ToText(VSTS_Source),Kind="Table"]}[Data],
    Format = Table.TransformColumnTypes(VSTS1_Table,{{"Account", type text}, {"Project", type text}})
in
    Format

Data Treatment:
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxmDSBEyagkkzMGkOJi2UYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [VSTS_Source_ID = _t]),
    Format = Table.TransformColumnTypes(Source,{{"VSTS_Source_ID", Int64.Type}}),
    VSTS_File_Link = Table.AddColumn(Format, "VSTS_Files", each VSTS_DataSource([VSTS_Source_ID])),
    Show_VSTS_Data = Table.ExpandTableColumn(VSTS_File_Link, "VSTS_Files", {"Account", "Project"}, {"VSTS_Files.Account", "VSTS_Files.Project"})
in
    Show_VSTS_Data

In file below see a PBI file and EXCEL base just change the path for the both option in the row SOURCE to where you save the excel file and everything should be working.

https://we.tl/E3BcVzzlPX

Regards,

MFelix