Power Query M Lookup on Same table with Filtering

0 votes

I already have an Excel Power Query that draws data from a "loading zone" table. Later, I want to add a macro that will cause the query to refresh, add the new data, and then delete the loading zone rows.

I required commenting to be available, and by self-referencing this query and extracting the manually made comments column, I was able to make commenting available. The query returns these and other rows.

RawQuery

With Power Query, I'm attempting to have it seek up comments for the same Device ID from the prior week.

As a result, "presenting concerns" should be Row 6's Last Week Comment value. There shouldn't be anything for the others since there were none last week.

The majority of available solutions, which I would prefer to avoid, are for Power BI and DAX setups.

Feb 2, 2023 in Others by Kithuzzz
• 38,010 points
1,170 views

1 answer to this question.

0 votes

In power query, merge the table on top of itself.

  • match Device ID on top to Device ID on bottom
  • match Last Week on top to Week on bottom
  • hit [OK] then expand the Comment field using the arrows atop the new column

    enter image description here

    let  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Merged Queries" = Table.NestedJoin(Source, {"Device ID", "Last Week"}, Source, {"Device ID", "Week"}, "Changed Type", JoinKind.LeftOuter),
    #"Expanded Changed Type" = Table.ExpandTableColumn(#"Merged Queries", "Changed Type", {"Comment"}, {"Last Weeks Comment"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Changed Type",{{"Date", type date}, {"Week", type date}, {"Last Week", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"ID", Order.Ascending}})
    in #"Sorted Rows"
    
    ID Device ID Date Week Last Week Comment
    1 777 26/01/2023 22/01/2023 15/01/2023 presenting
    2 856 27/01/2023 22/01/2023 15/01/2023
    3 968 29/01/2023 22/01/2023 15/01/2023
    4 652 29/01/2023 22/01/2023 15/01/2023
    5 621 30/01/2023 29/01/2023 22/01/2023
    6 777 02/01/2023 29/01/2023 22/01/2023 fixed

    enter image description here

answered Feb 2, 2023 by narikkadan
• 63,700 points

Related Questions In Others

0 votes
1 answer

Add column from another table using excel power query

Use a left outer join to combine ...READ MORE

answered Feb 24, 2023 in Others by narikkadan
• 63,700 points
929 views
0 votes
1 answer

Plot multiple datasets on the same chart with VBA

Try this: Sub PlotData() Dim ...READ MORE

answered Apr 6, 2023 in Others by narikkadan
• 63,700 points
476 views
0 votes
1 answer

Create dropdown list in excel that displays different values from the same lookup table

Looks like the Worksheet_Change() sub needs two ...READ MORE

answered Apr 6, 2023 in Others by narikkadan
• 63,700 points
378 views
0 votes
0 answers

Write a DAX query to obtain the bottom 5 customers based on the order price

Jan 22, 2020 in Others by anonymous
• 170 points
343 views
0 votes
1 answer

Excel Power Query: Using List.MatchAny on a column value

try this. let TableA = ...READ MORE

answered Oct 22, 2018 in Power BI by Annie97
• 2,160 points
3,954 views
0 votes
1 answer

Power Query : http POST request with form data

Try using Uri.BuildQueryString and Json.Document. let ...READ MORE

answered Dec 12, 2018 in Power BI by Upasana
• 8,620 points
8,431 views
0 votes
1 answer

Power Query : Adding Columns and Multiple files

The following call, FirstRowAsHeader = Table.PromoteHeaders(TableWithoutHeader) shall replace the ...READ MORE

answered Feb 14, 2019 in Power BI by Upasana
• 8,620 points
1,504 views
0 votes
1 answer

Power Query : making HTTP POST request with form data

Try Using Uri.BuildQueryString and Json.Document let ...READ MORE

answered Feb 14, 2019 in Power BI by Upasana
• 8,620 points
5,788 views
0 votes
1 answer

Excel Power Query import (same file but with different month name)

Use the name manager function in Excel ...READ MORE

answered Dec 13, 2022 in Others by narikkadan
• 63,700 points
770 views
0 votes
1 answer

How can I verify if a column is only decimal using Power Query M formula?

Try this: = Table.ReplaceValue(#"Changed Type",each [Column4], each ...READ MORE

answered Feb 13, 2023 in Others by narikkadan
• 63,700 points
642 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP