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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Anonymous
Not applicable

How to join to M queries

Greetings  I am new here, am not sure if this ha been posted eariler, am trying to pull data from OMS and pushing to PowerBI to get better better and custom visilization,  eariler i created a table stroage and pushed all my data to it, later on i connceted with power bi desktop, due partitionkey  and Rowkeys complex behavior i dropped the idea, 

Now i am trying to export the m queries from tw differnet workspaces and trying to join them and m not sure how do i get it done help is much appricaited 

 

where XXX is workspace 

<let AnalyticsQuery =
let Source = Json.Document(Web.Contents("https://api.loganalytics.io/v1/workspaces/XXXXXXXXX",
[Query=[#"query"="Update
| where OSType!=""Linux"" and Optional==false
| summarize hint.strategy=partitioned arg_max(TimeGenerated, *) by Computer,SourceComputerId,UpdateID
| where UpdateState=~""Needed"" and Approved!=false
| render table",#"x-ms-app"="OmsAnalyticsPBI",#"timespan"="2018-08-02T04:12:11.000Z/2018-08-31T19:12:11.000Z",#"prefer"="ai.response-thinning=true"],Timeout=#duration(0,0,4,0)])),
TypeMap = #table(
{ "AnalyticsTypes", "Type" },
{
{ "string", Text.Type },
{ "int", Int32.Type },
{ "long", Int64.Type },
{ "real", Double.Type },
{ "timespan", Duration.Type },
{ "datetime", DateTimeZone.Type },
{ "bool", Logical.Type },
{ "guid", Text.Type }
}),
DataTable = Source[tables]{0},
Columns = Table.FromRecords(DataTable[columns]),
ColumnsWithType = Table.Join(Columns, {"type"}, TypeMap , {"AnalyticsTypes"}),
Rows = Table.FromRows(DataTable[rows], Columns[name]),
Table = Table.TransformColumnTypes(Rows, Table.ToList(ColumnsWithType, (c) => { c{0}, c{3}}))
in
Table
in AnalyticsQuery>
1 ACCEPTED SOLUTION
v-yulgu-msft
Employee
Employee

Hi @Anonymous,

 

What do you mean join the M queries? Do you want to combine the two datasets returned by two queries? If so, you can load them separately into desktop, then, merge or append them in Query Editor via UI.

 

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

Add the queries as you normally would. Then in the relationships pane you can join the datasets together by specifying which columns you need to Join.

Tan

v-yulgu-msft
Employee
Employee

Hi @Anonymous,

 

What do you mean join the M queries? Do you want to combine the two datasets returned by two queries? If so, you can load them separately into desktop, then, merge or append them in Query Editor via UI.

 

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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