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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
SteveMueckschel
Frequent Visitor

Take over values of the last date with value

Hello together,

I am fairly new to Power Query. Maybe one of you can help me. I have used Power Query to create a table like this from different sources.

partnumberdatesourcestate
6102001120.01.2023 0
6102001220.01.2023 0
6102001320.01.2023 0
6102001420.01.2023 0
6102001520.01.2023 0
6102001119.01.2023 0
6102001219.01.2023 0
6102001319.01.2023 0
6102001419.01.2023 0
6102001519.01.2023 0
6102001118.01.20232023-01-18_Längsträger LI.xlsxok
6102001218.01.2023 0
6102001318.01.20232023-01-18_Längsträger LI.xlsxnot ok
6102001418.01.20232023-01-18_Längsträger LI.xlsxnot ok
6102001518.01.20232023-01-18_Längsträger LI.xlsxnot ok
6102001117.01.20232023-01-17_Längsträger LI.xlsxnot ok
6102001217.01.20232023-01-17_Längsträger LI.xlsxok
6102001317.01.20232023-01-17_Längsträger LI.xlsxnot ok
6102001417.01.20232023-01-17_Längsträger LI.xlsxnot ok
6102001517.01.20232023-01-17_Längsträger LI.xlsxnot ok
6102001116.01.20232023-01-16_Längsträger LI.xlsxnot ok
6102001216.01.20232023-01-16_Längsträger LI.xlsxnot ok
6102001316.01.20232023-01-16_Längsträger LI.xlsxnot ok
6102001416.01.20232023-01-16_Längsträger LI.xlsxnot ok
6102001516.01.20232023-01-16_Längsträger LI.xlsxnot ok
6102001115.01.2023 0
6102001215.01.2023 0
6102001315.01.2023 0
6102001415.01.2023 0
6102001515.01.2023 0

 

Now I want to create a column where for the unchecked dates the state of the last checked date for the part is entered. Additionally the last checked date should be added.

partnumberdatesourcestatestate2last checked
6102001120.01.2023 0(ok)18.01.2023
6102001220.01.2023 0(ok)17.01.2023
6102001320.01.2023 0(not ok)18.01.2023
6102001420.01.2023 0(not ok)18.01.2023
6102001520.01.2023 0(not ok)18.01.2023
6102001119.01.2023 0(ok)18.01.2023
6102001219.01.2023 0(ok)17.01.2023
6102001319.01.2023 0(not ok)18.01.2023
6102001419.01.2023 0(not ok)18.01.2023
6102001519.01.2023 0(not ok)18.01.2023
6102001118.01.20232023-01-18_Längsträger LI.xlsxokoknull
6102001218.01.2023 0(ok)17.01.2023
6102001318.01.20232023-01-18_Längsträger LI.xlsxnot oknot oknull
6102001418.01.20232023-01-18_Längsträger LI.xlsxnot oknot oknull
6102001518.01.20232023-01-18_Längsträger LI.xlsxnot oknot oknull
6102001117.01.20232023-01-17_Längsträger LI.xlsxnot oknot oknull
6102001217.01.20232023-01-17_Längsträger LI.xlsxokoknull
6102001317.01.20232023-01-17_Längsträger LI.xlsxnot oknot oknull
6102001417.01.20232023-01-17_Längsträger LI.xlsxnot oknot oknull
6102001517.01.20232023-01-17_Längsträger LI.xlsxnot oknot oknull
6102001116.01.20232023-01-16_Längsträger LI.xlsxnot oknot oknull
6102001216.01.20232023-01-16_Längsträger LI.xlsxnot oknot oknull
6102001316.01.20232023-01-16_Längsträger LI.xlsxnot oknot oknull
6102001416.01.20232023-01-16_Längsträger LI.xlsxnot oknot oknull
6102001516.01.20232023-01-16_Längsträger LI.xlsxnot oknot oknull
6102001115.01.2023 00null
6102001215.01.2023 00null
6102001315.01.2023 00null
6102001415.01.2023 00null
6102001515.01.2023 00null

 

I hope you get my problem.

I already tried to solve it with VBA, but my dataset will be more than two million rows. So I would like to calculate it in Power Query or Power Pivot.

 

Thanks in advance for your help

 

Warm greetings

Stephan

1 ACCEPTED SOLUTION
wdx223_Daniel
Super User
Super User

wdx223_Daniel_0-1674792090743.png

let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    Custom1 = let
                 group=Table.Group(Source,"partnumber",{"n",each Table.Skip(Table.Sort(_,{"date",1}),each [state]=0){0}?})
              in Table.FromRecords(
                                   Table.TransformRows(
                                                       Source,
                                                       each let
                                                               a=group{[partnumber=[partnumber]]}[n]??[],
                                                               b=if [state]<>0 or a=[] or a[date]>=[date] then [] else a
                                                            in _&[state2=b[state]? ??[state],last checked=b[date]?]
                                                       )
                                  )
in
    Custom1

View solution in original post

6 REPLIES 6
m_alireza
Solution Specialist
Solution Specialist

Hi @SteveMueckschel ,

I believe its because in your example, when there is nothing in the source column, you have null values, whereas in mine, it was just an empty cell so every reference to [source]="" or [source] <>"" isnt working. 

To make it work, amend your query to the following:  

 

let
    Source = Excel.Workbook(File.Contents("C:\Users\SMueckschel\Desktop\Test_Lösung.xlsx"), null, true),
    Table1_Table = Source{[Item="Tabelle1",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"partnumber", Int64.Type}, {"date", type nullable text meta [Serialized.Text = true]}, {"source", type nullable text meta [Serialized.Text = true]}, {"state", type nullable text meta [Serialized.Text = true]}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"partnumber"}, {{"maxdate", each List.Max([date]), type nullable date}, {"maxdatechecked", each List.Max(Table.SelectRows(_, each [source]<>null)[date]), type date}, {"AllRows", each _, type table [partnumber=nullable text, date=nullable date, source=nullable text, state=nullable text, Custom=number]}}),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"maxdate"}),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Removed Columns", "AllRows", {"date", "source", "state"}, {"date", "source", "state"}),
     #"Custom1" = Table.AddColumn(#"Expanded AllRows", "state2", each if ([date] >= [maxdatechecked]) and [source] = null then "(" & List.Max(Table.SelectRows(#"Expanded AllRows", each [date]=[maxdatechecked])[state]) & ")" else [state] ),
     #"Added Conditional Column" = Table.AddColumn(Custom1, "lastchecked", each if [maxdatechecked] < [date] and [source] = null then [maxdatechecked] else null),
     #"Removed Columns1" = Table.RemoveColumns(#"Added Conditional Column",{"maxdatechecked"})
in
    #"Removed Columns1"

Hi @m_alireza ,

sorry for my late respond.

I just noticed that there is still a mistake in your code. The last status of the certain part should always be taken over. In your code for some reason always "ok" is taken over.
But I have no idea what could be the reason for that.
Could you look again over "Custom 1".

Thanks in advance.

Greetings
Stephan

image.png

 

let
    Source = Excel.Workbook(File.Contents("C:\Users\SMueckschel\Desktop\Test_Lösung.xlsx"), null, true),
    Table1_Table = Source{[Item="Tabelle1",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"partnumber", Int64.Type}, {"date", type date}, {"source", type text}, {"state", type any}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"partnumber"}, {{"maxdate", each List.Max([date]), type nullable date}, {"maxdatechecked", each List.Max(Table.SelectRows(_, each [source]<>null)[date]), type date}, {"AllRows", each _, type table [partnumber=nullable text, date=nullable date, source=nullable text, state=nullable text, Custom=number]}}),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"maxdate"}),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Removed Columns", "AllRows", {"date", "source", "state"}, {"date", "source", "state"}),
    #"Custom1" = Table.AddColumn(#"Expanded AllRows", "state2", each if ([date] >= [maxdatechecked]) and [source] = null then "(" & List.Max(Table.SelectRows(#"Expanded AllRows", each [date]=[maxdatechecked])[state]) & ")" else [state] ),
    #"Added Conditional Column" = Table.AddColumn(Custom1, "lastchecked", each if [maxdatechecked] < [date] and [source] = null then [maxdatechecked] else null),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Conditional Column",{"maxdatechecked"})
in
    #"Removed Columns1"

 

wdx223_Daniel
Super User
Super User

wdx223_Daniel_0-1674792090743.png

let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    Custom1 = let
                 group=Table.Group(Source,"partnumber",{"n",each Table.Skip(Table.Sort(_,{"date",1}),each [state]=0){0}?})
              in Table.FromRecords(
                                   Table.TransformRows(
                                                       Source,
                                                       each let
                                                               a=group{[partnumber=[partnumber]]}[n]??[],
                                                               b=if [state]<>0 or a=[] or a[date]>=[date] then [] else a
                                                            in _&[state2=b[state]? ??[state],last checked=b[date]?]
                                                       )
                                  )
in
    Custom1

Hi @wdx223_Daniel ,

 

Sorry for my late respond.

Thanks for the solution. It took me a while to adjust the query to my database, because I do not completely understand what it's doing. 😅

I now have the problem that it takes about 15 minutes to refresh 5000 records. 

In the end my database will have more than two million rows. I am afraid that the computing power is not sufficient for this purpose.

Do you have any ideas on how to reduce the refresh time?

 

Greethings

Stephan

 

m_alireza
Solution Specialist
Solution Specialist

Hi @SteveMueckschel , 

Please copy and paste this query in your advanced editor and adjust as needed to match your database:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZRBCoMwEEWvIllrmEky0R6h4A1EuiouWhTUhQfyJl5MzaJUKg7agUAIPN78+YsUhfIIBgBRxcqABtQGjF0eywFVxh/AcIDlAMcBxAFrSLwxIQ8BywGOA4gDQsjsC1ivBDDB7JFPY111fTuN1bON8rse3t2wIM3rZ4uM2+LkiLrpo+0YJ2IhEUsoLd2zpCcs5oJla7AiOZyIhUQsoVm/Z/Fnm/3bYkUsTsRCIpbQLnE/0hFgOcBxAF0Eyhk=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [partnumber = _t, date = _t, source = _t, state = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"partnumber"}, {{"maxdate", each List.Max([date]), type nullable date}, {"maxdatechecked", each List.Max(Table.SelectRows(_, each [source]<>"")[date]), type date}, {"AllRows", each _, type table [partnumber=nullable text, date=nullable date, source=nullable text, state=nullable text, Custom=number]}}),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"maxdate"}),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Removed Columns", "AllRows", {"date", "source", "state"}, {"date", "source", "state"}),
    Custom1 = Table.AddColumn(#"Expanded AllRows", "state2", each if ([date] >= [maxdatechecked]) and [source] = "" then "(" & List.Max(Table.SelectRows(#"Expanded AllRows", each [date]=[maxdatechecked])[state]) & ")" else [state] ),
    #"Added Conditional Column" = Table.AddColumn(Custom1, "lastchecked", each if [maxdatechecked] < [date] and [source] = "" then [maxdatechecked] else null),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Conditional Column",{"maxdatechecked"})
in
    #"Removed Columns1"


Sample output (grouped by partnumber):
maxselectrowsmultipleconditions.png

Hi @m_alireza ,

thank you so much for helping me with my problem.

I copied your query but it does not work properly. Maby you can show me my mistake.

This is my code.

 

let
    Source = Excel.Workbook(File.Contents("C:\Users\SMueckschel\Desktop\Test_Lösung.xlsx"), null, true),
    Table1_Table = Source{[Item="Tabelle1",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"partnumber", Int64.Type}, {"date", type nullable text meta [Serialized.Text = true]}, {"source", type nullable text meta [Serialized.Text = true]}, {"state", type nullable text meta [Serialized.Text = true]}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"partnumber"}, {{"maxdate", each List.Max([date]), type nullable date}, {"maxdatechecked", each List.Max(Table.SelectRows(_, each [source]<>"")[date]), type date}, {"AllRows", each _, type table [partnumber=nullable text, date=nullable date, source=nullable text, state=nullable text, Custom=number]}}),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"maxdate"}),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Removed Columns", "AllRows", {"date", "source", "state"}, {"date", "source", "state"}),
     #"Custom1" = Table.AddColumn(#"Expanded AllRows", "state2", each if ([date] >= [maxdatechecked]) and [source] = "" then "(" & List.Max(Table.SelectRows(#"Expanded AllRows", each [date]=[maxdatechecked])[state]) & ")" else [state] ),
     #"Added Conditional Column" = Table.AddColumn(Custom1, "lastchecked", each if [maxdatechecked] < [date] and [source] = "" then [maxdatechecked] else null),
     #"Removed Columns1" = Table.RemoveColumns(#"Added Conditional Column",{"maxdatechecked"})
in
    #"Removed Columns1"

 

as you see in this screenshot it does not work properly:

image.png

 I guess its because the maxdatachecked is not working, but I can't figure out why the wrong date is filtered.

image.png

is it because of a wrong datatype?

 

Thank you for your help

 

Greethings

Stephan

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors