Workaround to retrieve dataflow results in Power Query in Excel.

PowerBI.DataflowsWorkaround.pq

let func =   
 (
  BlobStorageName as text,
  CsvSnapshotFolderName as text,
  optional optionalLocale as text,
  optional optionalDelimiter as text,
  optional optionalEncoding as number
) =>
  let
    Locale = if optionalLocale = null then "en-US" else optionalLocale,
    Delimiter = if optionalDelimiter = null then "," else optionalDelimiter,
    Encoding = if optionalEncoding = null then 1252 else Number.From(optionalEncoding),
    fnTypeAsText = (TypeAsText as text) =>
      Record.Field(
        [
          boolean = type logical,
          int64 = Int64.Type,
          time = type time,
          date = type date,
          dateTime = type datetime,
          dateTimeOffset = type datetimezone,
          double = type number,
          decimal = Percentage.Type,
          //duration = type duration, -- current bug in dataflow
          string = type text
        ],
        TypeAsText
      ),
    Source = AzureStorage.Blobs(BlobStorageName),
    powerbi1 = Source{[Name = "powerbi"]}[Data],
    FilterFolderName = Table.SelectRows(powerbi1, each Text.Contains([Name], CsvSnapshotFolderName)),
    FilterOutSnapshotExtension = Table.SelectRows(
      FilterFolderName,
      each ([Extension] <> ".snapshots")
    ),
    SortDescendingAndGrabFirst = Table.Buffer(
      Table.Sort(FilterOutSnapshotExtension, {{"Date modified", Order.Descending}})
    ){0},
    CsvContent = SortDescendingAndGrabFirst[Content],
    #"Imported CSV" = Csv.Document(
      CsvContent,
      [Delimiter = Delimiter, Encoding = Encoding, QuoteStyle = QuoteStyle.None]
    ),
    JsonPath = Text.BeforeDelimiter(CsvSnapshotFolderName, "/", 1),
    GetModelJSON = Table.SelectRows(powerbi1, each [Name] = JsonPath & "/model.json")[Content]{0},
    #"Imported JSON" = Json.Document(GetModelJSON, Encoding),
    entities = #"Imported JSON"[entities],
    #"Converted to Table" = Table.FromList(
      entities,
      Splitter.SplitByNothing(),
      null,
      null,
      ExtraValues.Error
    ),
    #"Expanded Column1" = Table.ExpandRecordColumn(
      #"Converted to Table",
      "Column1",
      {"$type", "name", "description", "pbi:refreshPolicy", "attributes", "partitions"}
    ),
    FilterRelevantFile = Table.SelectRows(
      #"Expanded Column1",
      each ([name] = Text.BetweenDelimiters(CsvSnapshotFolderName, "/", ".", 1))
    ),
    #"Removed Other Columns" = Table.SelectColumns(FilterRelevantFile, {"attributes"}),
    #"Expanded attributes" = Table.ExpandListColumn(#"Removed Other Columns", "attributes"),
    #"Expanded attributes1" = Table.ExpandRecordColumn(
      #"Expanded attributes",
      "attributes",
      {"name", "dataType"}
    ),
    NewColumnNames = #"Expanded attributes1"[name],
    RenamedColumns = Table.RenameColumns(
      #"Imported CSV",
      List.Zip({Table.ColumnNames(#"Imported CSV"), NewColumnNames})
    ),
    #"Changed Type" = Table.TransformColumnTypes(
      RenamedColumns,
      List.Zip(
        {
          Table.ColumnNames(RenamedColumns),
          List.Transform(#"Expanded attributes1"[dataType], fnTypeAsText)
        }
      ),
      Locale
    )
  in
    #"Changed Type" ,
documentation = [
Documentation.Name =  " PowerBI.DataflowsWorkaround ",
Documentation.Description = " Workaround to retrieve dataflow results in Power Query in Excel. ",
Documentation.LongDescription = " Workaround to retrieve dataflow results in Power Query in Excel. ",
Documentation.Category = " Accessing Data Functions ",
Documentation.Source = "  www.TheBIccountant.com, see:  https://wp.me/p6lgsG-2n4 .  ",
Documentation.Version = " 1.0 ",
Documentation.Author = " Imke Feldmann ",
Documentation.Examples = {[Description =  "  ",
Code = "  ",
Result = "  "]}]
  
 in  
  Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))