Forum Discussion
Helper ICreating a custom column that returns max value of another column in the same table
Here is an approach which doesn't break Direct Query mode. Replace first 3 lines appropriately (i.e. Source, Test, dbo_Test1).
In 4th & 5th line replace dbo_Test1.
let Source = Sql.Databases("XXXXXX\SQLEXPRESS"), Test = Source{[Name="Test"]}[Data], dbo_Test1 = Test{[Schema="dbo",Item="Test1"]}[Data], #"Grouped Rows" = Table.Group(dbo_Test1, {"ID"}, {{"A Job", each List.Min([Start]), type datetime}}), #"Merged Queries" = Table.NestedJoin(dbo_Test1, {"ID", "Start"}, #"Grouped Rows", {"ID", "A Job"}, "Grouped Rows", JoinKind.LeftOuter), #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"A Job"}, {"A Job"}) in #"Expanded Grouped Rows"
Most Valuable ProfessionalIn Power Query UI, in Home tab on extreme right, you will see Enter Data. Here, you can enter data manually rather than fetching from a Source. This will generate that type of code for the Source which you are using.
You would need to input your data using Enter data or get data from somewhere. If you use Enter data, you can't edit it to add or delete or update. Once you do it, you will get a source line in your Advanced editor. Now copy this source line and replace my source line in my code.
Helper IThe table I am trying to make these changes to is a direct query to an sql database (it updates in real time). I am a bit confused as to how will this separate manually entered table in a blank query make changes to my real table. Am I able to connect the two?
- Vijay_A_Verma
Let's use your code after making connection to SQL Server is following
let Source = Sql.Databases("XXXXXXX\SQLEXPRESS"), Sample = Source{[Name="Sample"]}[Data], dbo_Sales = Sample{[Schema="dbo",Item="Sales"]}[Data] in dbo_SalesRemove last 2 lines from here so you are left with only this
let Source = Sql.Databases("XXXXXXX\SQLEXPRESS"), Sample = Source{[Name="Sample"]}[Data], dbo_Sales = Sample{[Schema="dbo",Item="Sales"]}[Data]This is equivalent to Source statement when your import from SQL server. Some sources generate a single line and here SQL server has generated 3 lines for Source.
Now, you can copy my code after Changed Type and the code will become. If you need to Changed Type, do it after dbo_Sales.
let Source = Sql.Databases("XXXXXXX\SQLEXPRESS"), Sample = Source{[Name="Sample"]}[Data], dbo_Sales = Sample{[Schema="dbo",Item="Sales"]}[Data], #"Added Index" = Table.AddIndexColumn(dbo_Sales, "Index", 0, 1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each List.Min(Table.SelectRows(#"Added Index", (x)=>x[ID]=[ID])[Start])), #"Added Custom1" = Table.AddColumn(#"Added Custom", "A Job", each try if [ID]=#"Added Index"[ID]{[Index]-1} then null else [Custom] otherwise [Custom]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Index", "Custom"}) in #"Removed Columns"- drogzy
Thank you, I understand now but the thing is as soon as I add an index column it breaks my DirectQuery mode forcing me to switch all tables to import mode.
Any other suggestions? maybe while adding a custom column?
- Vijay_A_Verma
Here is an approach which doesn't break Direct Query mode. Replace first 3 lines appropriately (i.e. Source, Test, dbo_Test1).
In 4th & 5th line replace dbo_Test1.
let Source = Sql.Databases("XXXXXX\SQLEXPRESS"), Test = Source{[Name="Test"]}[Data], dbo_Test1 = Test{[Schema="dbo",Item="Test1"]}[Data], #"Grouped Rows" = Table.Group(dbo_Test1, {"ID"}, {{"A Job", each List.Min([Start]), type datetime}}), #"Merged Queries" = Table.NestedJoin(dbo_Test1, {"ID", "Start"}, #"Grouped Rows", {"ID", "A Job"}, "Grouped Rows", JoinKind.LeftOuter), #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"A Job"}, {"A Job"}) in #"Expanded Grouped Rows"