1 ACCEPTED SOLUTION
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 @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
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"
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
Custom1Hi @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):
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:
I guess its because the maxdatachecked is not working, but I can't figure out why the wrong date is filtered.
is it because of a wrong datatype?
Thank you for your help
Greethings
Stephan
Helpful resources
Microsoft Fabric Learn Together
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Power BI Monthly Update - April 2024
Check out the April 2024 Power BI update to learn about new features.
Fabric Community Update - April 2024
Find out what's new and trending in the Fabric Community.
