Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I hope you can help me with the following question:
I have a table who has only a start date. The end date of the production is the next start date of the production of the group customer/contract. Can you tell me what code I need to add to Power Query to add a column to have an extra column with the end date?
Solved! Go to Solution.
The attached code is an alternate method that presumes you are using Excel Power Query. You can change the source to adapt to your data. The function OffsetCol is an inline function that efficiently gets the next row. A custom column is then used to detect if the Customer and Contract changes.
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
//(Source as table, ColName as text, optional offset as number) =>
OffsetCol = ( Source as table, RecordColumnName as text, optional Columns as list, optional offset as number ) =>
let
offset = if offset = null then -1 else offset,
// Prefix = if prefix = null then "Prior" else prefix,
Columns = List.Buffer(if Columns = null then Table.ColumnNames(Source) else Columns),
SourceSelect = if Columns = null then Source else Table.SelectColumns(Source,Columns),
ShiftedList = if offset < 0 then List.Repeat({null}, -offset) & List.RemoveLastN(Table.ToRecords(SourceSelect ),-offset)
else List.RemoveFirstN(Table.ToRecords(SourceSelect ),offset) & List.Repeat({null}, offset),
Combine1 = Table.ToColumns(Source) & {ShiftedList},
Combine2 = Table.FromColumns(Combine1, Table.ColumnNames(Source) & {RecordColumnName} )
in
Combine2,
Result = OffsetCol(Source, "NextRecord", {"Start Date", "Customer","Contract"},1),
#"Added Custom" = Table.AddColumn(Result, "EndDate", each if [NextRecord]= null or [NextRecord][Contract] <> [Contract] or [NextRecord][Customer] <> [Customer] then null else [NextRecord][Start Date]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"NextRecord"})
in
#"Removed Columns"
The attached code is an alternate method that presumes you are using Excel Power Query. You can change the source to adapt to your data. The function OffsetCol is an inline function that efficiently gets the next row. A custom column is then used to detect if the Customer and Contract changes.
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
//(Source as table, ColName as text, optional offset as number) =>
OffsetCol = ( Source as table, RecordColumnName as text, optional Columns as list, optional offset as number ) =>
let
offset = if offset = null then -1 else offset,
// Prefix = if prefix = null then "Prior" else prefix,
Columns = List.Buffer(if Columns = null then Table.ColumnNames(Source) else Columns),
SourceSelect = if Columns = null then Source else Table.SelectColumns(Source,Columns),
ShiftedList = if offset < 0 then List.Repeat({null}, -offset) & List.RemoveLastN(Table.ToRecords(SourceSelect ),-offset)
else List.RemoveFirstN(Table.ToRecords(SourceSelect ),offset) & List.Repeat({null}, offset),
Combine1 = Table.ToColumns(Source) & {ShiftedList},
Combine2 = Table.FromColumns(Combine1, Table.ColumnNames(Source) & {RecordColumnName} )
in
Combine2,
Result = OffsetCol(Source, "NextRecord", {"Start Date", "Customer","Contract"},1),
#"Added Custom" = Table.AddColumn(Result, "EndDate", each if [NextRecord]= null or [NextRecord][Contract] <> [Contract] or [NextRecord][Customer] <> [Customer] then null else [NextRecord][Start Date]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"NextRecord"})
in
#"Removed Columns"
Hi @Anonymous
Try this
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WqlTSUaoAYiNDfQNDfSMDQ0ulWB0kYQuswgYm+gZGmMKGhtiFLVCEq4BC5SBhc+zCpvoGxliELRDCsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Customer = _t, Contract = _t, #"Start Date" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"Contract", type text}, {"Start Date", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "End Date", each let
sDate = Number.From( [Start Date] ),
selectRows = Table.SelectRows( #"Changed Type", (r) =>
Number.From( r[Start Date] ) > sDate
and r[Customer] = [Customer]
and r[Contract] = [Contract]
)
in
List.Min( selectRows[Start Date] ))
in
#"Added Custom"
Hi,
After changing the formulatu I still get an error.
I fixed that I can group the records and put an index column in the table for each contract. In dax I can make a column where I can take the begin date as an end date but I would like to have it in Power Query.
Do you have a solution after I add the index?
Thanks,
Mario
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.