none
Joining two M queries RRS feed

  • Question

  • Hi,

    I got some excellent help in this forum with the two queries below. They work perfectly on their own but I haven't managed to join them together, getting a Token Eof expected error each time I solve the previous error, and the error message tells me nothing. I guess the problem is an easy one and some help would be highly appreciated!

    Thanks!

    Caj

    Query 1

    let
         Source = Excel.CurrentWorkbook(){[Name="Tabell1"]}[Content],
        DuplicatedColumn = Table.DuplicateColumn(Source, "Start", "Copy of Start"),
        DuplicatedColumn1 = Table.DuplicateColumn(DuplicatedColumn, "End", "Copy of End"),
        ReorderedColumns = Table.ReorderColumns(DuplicatedColumn1,{"Nr", "Copy of Start", "Copy of End", "Start", "End", "Activity"}),
        RenamedColumns = Table.RenameColumns(ReorderedColumns,{{"Copy of Start", "StartOrg"}, {"Copy of End", "EndOrg"}}),
        Records = Table.ToRecords(RenamedColumns),
         DateTime.IsSameDay = (x, y) => Date.Year(x) = Date.Year(y) and Date.Month(x) = Date.Month(y) and Date.Day(x) = Date.Day(y),
         Expand = (x) => List.Generate(
             () => Record.Combine({x, [End=Date.EndOfDay(x[Start])]}),
             (record) => record[Start] <= x[End],
             (record) => let
                 NextStart = Date.StartOfDay(Date.AddDays(record[Start], 1)),
                 NextEnd = Date.EndOfDay(NextStart),
                 ThisEnd = List.Min({NextEnd, x[End]})
             in
                 Record.Combine({record, [Start=NextStart, End=ThisEnd]})),
         Transformed = List.Transform(Records, each if DateTime.IsSameDay([Start], [End]) then {_} else Expand(_)),
         Combined = List.Combine(Transformed),
         Result = Table.FromRecords(Combined),
        InsertedCustom = Table.AddColumn(Result, "Custom", each [End]-[Start])

    in
        InsertedCustom

    Query 2

    let
        Tabell1 = let
        Source = Excel.CurrentWorkbook(){[Name="Tabell1"]}[Content],
         CombineTwoRows = (x, y) =>
    let
                 combine = x[Vehicle] = y[Vehicle] and x[End] < y[Start],
                 added = Record.Combine({x, [Start=x[End], End=y[Start], Activity="Inactive"]}),
                 result = if combine then {added, y} else {y}
             in result,
         GenerateStandingRows = (table, combine) =>
             let
                 Handle = (x, y) => {x, y},
                 buffered = Table.Buffer(table),
                 n = Table.RowCount(buffered),
                 windows = List.Generate(
                     () => {1, {buffered{0}}},
                     (x) => x{0} <= n,
                     (x) => {x{0} + 1, if x{0} < n then combine(buffered{x{0}-1}, buffered{x{0}}) else {buffered{x{0}}}},
                     (x) => x{1})
             in
                 windows,
         InsertInactivity = (table) => Table.FromRecords(List.Combine(GenerateStandingRows(table, CombineTwoRows))),
         TestData = Source
     in
         InsertInactivity(TestData)
    in
        Tabell1


    Csten

    Tuesday, September 2, 2014 6:42 AM

Answers

  • No, you can't put all that code in a single query. What I think you want to do is this:

    Keep the two queries separate. Let's say the first query is called Query1 and the second is called Query2. Then in the third line of Query2 where you currently have the expression:

    Source = Excel.CurrentWorkbook(){[Name="Tabell1"]}[Content],

    Change that to:

    Source = Query1,

    This means that instead of using the contents of the Excel table Tabell1 as the source data for the second query, you are using the output of the query called Query1. Does this work?

    Chris


    Check out my MS BI blog I also do SSAS, PowerPivot, MDX and DAX consultancy and run public SQL Server and BI training courses in the UK

    Tuesday, September 2, 2014 1:42 PM

All replies

  • Hi Caj,

    What do you mean by "join them together" exactly? Can you post the code that gives you the error message?

    Chris


    Check out my MS BI blog I also do SSAS, PowerPivot, MDX and DAX consultancy and run public SQL Server and BI training courses in the UK

    Tuesday, September 2, 2014 11:44 AM
  • Hi,

    I would like to take the output from query one and use that as input in query two. What I tried was basically to continue with query 2 after query one and adjust the first rows of query 2 slightly to get one query instead of two, se below. Is that approach possible at all?

    In the example below the error code occurs between row 25 and 26.

    Caj

    let
         Source = Excel.CurrentWorkbook(){[Name="Tabell1"]}[Content],
        DuplicatedColumn = Table.DuplicateColumn(Source, "Start", "Copy of Start"),
        DuplicatedColumn1 = Table.DuplicateColumn(DuplicatedColumn, "End", "Copy of End"),
        ReorderedColumns = Table.ReorderColumns(DuplicatedColumn1,{"Nr", "Copy of Start", "Copy of End", "Start", "End", "Activity"}),
        RenamedColumns = Table.RenameColumns(ReorderedColumns,{{"Copy of Start", "StartOrg"}, {"Copy of End", "EndOrg"}}),
        Records = Table.ToRecords(RenamedColumns),
         DateTime.IsSameDay = (x, y) => Date.Year(x) = Date.Year(y) and Date.Month(x) = Date.Month(y) and Date.Day(x) = Date.Day(y),
         Expand = (x) => List.Generate(
             () => Record.Combine({x, [End=Date.EndOfDay(x[Start])]}),
             (record) => record[Start] <= x[End],
             (record) => let
                 NextStart = Date.StartOfDay(Date.AddDays(record[Start], 1)),
                 NextEnd = Date.EndOfDay(NextStart),
                 ThisEnd = List.Min({NextEnd, x[End]})
             in
                 Record.Combine({record, [Start=NextStart, End=ThisEnd]})),
         Transformed = List.Transform(Records, each if DateTime.IsSameDay([Start], [End]) then {_} else Expand(_)),
         Combined = List.Combine(Transformed),
         Result = Table.FromRecords(Combined),
        InsertedCustom = Table.AddColumn(Result, "Custom", each [End]-[Start])
    in
        InsertedCustom
    //End query one
    let
        InsertedCustom = let
         CombineTwoRows = (x, y) =>
    let
                 combine = x[Vehicle] = y[Vehicle] and x[End] < y[Start],
                 added = Record.Combine({x, [Start=x[End], End=y[Start], Activity="Inactive"]}),
                 result = if combine then {added, y} else {y}
             in result,
         GenerateStandingRows = (table, combine) =>
             let
                 Handle = (x, y) => {x, y},
                 buffered = Table.Buffer(table),
                 n = Table.RowCount(buffered),
                 windows = List.Generate(
                     () => {1, {buffered{0}}},
                     (x) => x{0} <= n,
                     (x) => {x{0} + 1, if x{0} < n then combine(buffered{x{0}-1}, buffered{x{0}}) else {buffered{x{0}}}},
                     (x) => x{1})
             in
                 windows,
         InsertInactivity = (table) => Table.FromRecords(List.Combine(GenerateStandingRows(table, CombineTwoRows))),
         TestData = Source
     in
         InsertInactivity(TestData)
    in
        Tabell1


    Csten

    Tuesday, September 2, 2014 1:22 PM
  • No, you can't put all that code in a single query. What I think you want to do is this:

    Keep the two queries separate. Let's say the first query is called Query1 and the second is called Query2. Then in the third line of Query2 where you currently have the expression:

    Source = Excel.CurrentWorkbook(){[Name="Tabell1"]}[Content],

    Change that to:

    Source = Query1,

    This means that instead of using the contents of the Excel table Tabell1 as the source data for the second query, you are using the output of the query called Query1. Does this work?

    Chris


    Check out my MS BI blog I also do SSAS, PowerPivot, MDX and DAX consultancy and run public SQL Server and BI training courses in the UK

    Tuesday, September 2, 2014 1:42 PM
  • Thanks Chris!

    Your solution works perfectly! It takes along time for Excel to process the queries, with less then 10 000 rows as input. Can I do anything to improve that or are the queries that demanding when it comes to capacity?

    I got your book yesterday and am reading with great interest.

    //Caj


    Csten


    • Edited by csten Thursday, September 4, 2014 11:04 AM
    Thursday, September 4, 2014 9:10 AM
  • I think the performance is due to the complexity of the queries. It might be possible to improve performance by rewriting the queries, but it would be very difficult to suggest how to do this without the original workbook to experiment on.

    Chris


    Check out my MS BI blog I also do SSAS, PowerPivot, MDX and DAX consultancy and run public SQL Server and BI training courses in the UK

    Thursday, September 4, 2014 11:15 AM
  • Ok, thanks!

    /Caj


    Csten

    Thursday, September 4, 2014 11:16 AM