Power Query - connect to Dataset Tables

Power Query M Code Showcase
1

Hello,
I am trying to pull Dataset tables using Power Query.

There is this new feature that allows you to pull Dataset tables into Excel:
https://powerbi.microsoft.com/en-us/blog/announcing-new-ways-to-create-connected-tables-in-excel-connected-to-power-bi/
but it uses the “legacy” connections, instead of Power Query

image
image698×708 20.2 KB

This connection is blazing fast, it loads 100 000 rows in just a few seconds.
I would like to pull this table directly to Power Query so that I can then do some more data transformation while using this fast connection capabilities.

Any ideas on how to pull this data into Power Query ?

2

I know this OP has been around a while, recently I have been looking for solutions to get the data to excel and AI has suggested the following. I even had another small table to contain parameters for the query that Power Query would absorb within the one query and place into the Dax. Also once in PQ transformations are again available in the quick example below I’ve renamed the headers. I’m currently on a search for more info on this for other ideas, but struggling to find. I’m now trawling these forums.

let
Dax =
"EVALUATE
VAR EndDate = EOMONTH(TODAY(), 0)
RETURN
SUMMARIZECOLUMNS(
// grouping keys
Plant[Plant],
MaterialMaster[Material],
MaterialMaster[Material Description],

// filters
FILTER(ALL('Dates'),
    'Dates'[Date] >= EDATE(EndDate, -24) &&
    'Dates'[Date] <= EndDate
),
TREATAS({""AU""}, Plant[Plant]),

// measures
""Total Qty"", [Total Qty]

)
ORDER BY MaterialMaster[Material]
",

Source = AnalysisServices.Database(
“powerbi://api.powerbi.com/v1.0/myorg/Your Workspace”,
“Your Dataset”,
[Query = Dax, Implementation = “2.0”]
),

// tidy column names for Excel
Renamed = Table.RenameColumns(Source, {
{“Plant[Plant]”, “Plant”},
{“MaterialMaster[Material]”, “Material”},
{“MaterialMaster[Material Description]”, “Description”},
{“Total Qty”, “Total Qty”}
}, MissingField.Ignore)
in
Renamed