Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
mahesh_marathe
Regular Visitor

Can we add another dataset in Advance Editor window

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 

3 REPLIES 3
MFelix
Super User
Super User

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

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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

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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors