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.
Hi
I have a large table (83 Cols, 2M+ Rows) is that large these days?, I need to find the rows where the field 'productcode' is between 8000 and 9000, generate a duplicate row for each but with the 'createdDate' field set to one month earlier, and then again prior to that etc all the way back to the start of the uk fiscal year i.e. 1st April.
I sort of have parts of the picture using [createdDate = Date.AddMonths([createdDate],-1)] to reduce the month by one and using Table.insertrows for an additional row however with 83 cols i dont fancy specifying each and every single one! Is there a way in power query to just duplicate the entire row and change the createdDate to the month before?
Solved! Go to Solution.
Hi @JohnSalt,
You can try to manipulate the rows as records, but I think that a simpler approach will be to:
1. Filter the rows you need to duplicate.
2. Change the values.
3. Create a union of the original table with the changed rows.
look at this code
let
Source = #table( type table [value1=text,value2=text,date=date], {{"a","aa",#date(2000,1,1)},{"b","bb",#date(2000,2,1)},{"c","cc",#date(2000,3,1)}}),
#"Filtered Rows" = Table.SelectRows(Source, each ([date] >= #date(2000, 2, 1))),
#"Replaced Value" = Table.ReplaceValue(#"Filtered Rows",each _[date],each Date.AddMonths(_[date],-1),Replacer.ReplaceValue,{"date"}),
RestoreType = Value.ReplaceType(#"Replaced Value",Value.Type(#"Filtered Rows")),
#"Appended Query" = Table.Combine({Source, RestoreType})
in
#"Appended Query"
Start:
End:
You will need to check the performance of this operation for your case.
Hi @JohnSalt,
You can try to manipulate the rows as records, but I think that a simpler approach will be to:
1. Filter the rows you need to duplicate.
2. Change the values.
3. Create a union of the original table with the changed rows.
look at this code
let
Source = #table( type table [value1=text,value2=text,date=date], {{"a","aa",#date(2000,1,1)},{"b","bb",#date(2000,2,1)},{"c","cc",#date(2000,3,1)}}),
#"Filtered Rows" = Table.SelectRows(Source, each ([date] >= #date(2000, 2, 1))),
#"Replaced Value" = Table.ReplaceValue(#"Filtered Rows",each _[date],each Date.AddMonths(_[date],-1),Replacer.ReplaceValue,{"date"}),
RestoreType = Value.ReplaceType(#"Replaced Value",Value.Type(#"Filtered Rows")),
#"Appended Query" = Table.Combine({Source, RestoreType})
in
#"Appended Query"
Start:
End:
You will need to check the performance of this operation for your case.
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.