Forum Discussion
Continued ContributorM code to get value which was active on specific date
This is my biiiiig table. It holds all the changes made to a database table together with the date.
I don't need to import the whole table. I only need the "Status" value of every "Name" on two specific dates - 20190902 and 20191002. This is how the table should look after importing it:
What M code would do it?
After the whole day of thinking, trial and error, I came up with something acceptable. Thes M code is not as difficult as DAX. I like it. Kudos to SteveCampbell who encouraged me to try out UI.
let Source = Excel.Workbook(File.Contents("C:\Temp\PBI_example.xlsx"), null, true), Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data], #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"Date", Int64.Type}, {"Name", type text}, {"Status", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "DateGroup", each if [Date] <= 20191002 then if [Date] <= 20190902 then 20190902 else 20191002 else 0), #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Added Custom", {{"Date", type text}}, "en-US"),{"Date", "Status"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"DateStatus"), #"Grouped Rows" = Table.Group(#"Merged Columns", {"Name", "DateGroup"}, {{"MaxDateStatus", each List.Max([DateStatus]), type text}}), #"Split Column by Delimiter" = Table.SplitColumn(#"Grouped Rows", "MaxDateStatus", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"MaxDateStatus.1", "MaxDateStatus.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"MaxDateStatus.1", Int64.Type}, {"MaxDateStatus.2", type text}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"MaxDateStatus.1"}), #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"DateGroup", "Name", "MaxDateStatus.2"}) in #"Reordered Columns"This shows the status only if it was changed from previous timestamp. I will be able to calculate the remaining one status using Power BI. Using this M code I will only import the data which I need, avoiding tons of useless rows.
5 Replies
- SteveCampbell
Memorable Member
You shouldn't need to write the code specifically, load it into the UI and user the filters on the column headers.
Zyg_D
SteveCampbell wrote:You shouldn't need to write the code specifically, load it into the UI and user the filters on the column headers.
You may be right. I ask for assistance, because I am failing to create the desired table on my own. It is not a simple filter. It must also take into account data on another column... I tried grouping and sorting on several columns, but so far no luck. I would greatly appreciate your help.
- amitchandak
Super User
Zyg_D , while adding a connection from the database you can give a query(under advance property). For this table create a connection and add the query.
- Zyg_D
amitchandak wrote:Zyg_D, while adding a connection from the database you can give a query(under advance property). For this table create a connection and add the query.
Hello, amitchandak . Thank you for the screenshot. The primary source of the data is indeed the database. However, I get this data from a Power BI dataflow. This option does not allow me to input SQL query. I suppose, the only option I have is M code? I want to stress that I do not want to import the whole table into my model.
- Zyg_D
After the whole day of thinking, trial and error, I came up with something acceptable. Thes M code is not as difficult as DAX. I like it. Kudos to SteveCampbell who encouraged me to try out UI.
let Source = Excel.Workbook(File.Contents("C:\Temp\PBI_example.xlsx"), null, true), Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data], #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"Date", Int64.Type}, {"Name", type text}, {"Status", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "DateGroup", each if [Date] <= 20191002 then if [Date] <= 20190902 then 20190902 else 20191002 else 0), #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Added Custom", {{"Date", type text}}, "en-US"),{"Date", "Status"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"DateStatus"), #"Grouped Rows" = Table.Group(#"Merged Columns", {"Name", "DateGroup"}, {{"MaxDateStatus", each List.Max([DateStatus]), type text}}), #"Split Column by Delimiter" = Table.SplitColumn(#"Grouped Rows", "MaxDateStatus", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"MaxDateStatus.1", "MaxDateStatus.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"MaxDateStatus.1", Int64.Type}, {"MaxDateStatus.2", type text}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"MaxDateStatus.1"}), #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"DateGroup", "Name", "MaxDateStatus.2"}) in #"Reordered Columns"This shows the status only if it was changed from previous timestamp. I will be able to calculate the remaining one status using Power BI. Using this M code I will only import the data which I need, avoiding tons of useless rows.
