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.
I'd like to duplicate rows when a column has the value "Weekly", for each week from the "Date" to present.
Event | Recurring | Date |
Event A | Weekly | 22/09/2020 |
Event B | No | 14/08/2020 |
Event C | No | 15/03/2020 |
How can I use Power Query to add rows for "Weekly" for each week since the "Date", and have the Date reflect that?
Something like
Event A | Weekly | 22/09/2020 |
Event A | Weekly | 29/09/2020 |
Event A | Weekly | 06/10/2020 |
Event A | Weekly | 13/10/2020 |
Event A | Weekly | 20/10/2020 |
Event B | No | 14/08/2020 |
Event C | No | 15/03/2020 |
Solved! Go to Solution.
@someguy Add several steps to the previous post as below:
let
Source = #table({"Event","Recurring","Date"},{{"Event A","Weekly","9/22/2020"},{"Event B","No","8/14/2020"},{"Event C","No","3/15/2020"}}),
#"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Date", type date}}, "en-US"),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type with Locale", "Custom", each if [Recurring] = "Weekly" then List.Generate(()=>[Date], each _ <= Date.From(DateTime.FixedLocalNow()), each _ + #duration(7,0,0,0)) else null),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Conditional Column", "Custom"),
#"Added Conditional Column1" = Table.AddColumn(#"Expanded Custom", "Custom.1", each if [Recurring] = "Weekly" then [Custom] else [Date]),
#"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column1",{"Date", "Custom"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom.1", "Date"}})
in
#"Renamed Columns"
Result:
@someguy Add several steps to the previous post as below:
let
Source = #table({"Event","Recurring","Date"},{{"Event A","Weekly","9/22/2020"},{"Event B","No","8/14/2020"},{"Event C","No","3/15/2020"}}),
#"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Date", type date}}, "en-US"),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type with Locale", "Custom", each if [Recurring] = "Weekly" then List.Generate(()=>[Date], each _ <= Date.From(DateTime.FixedLocalNow()), each _ + #duration(7,0,0,0)) else null),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Conditional Column", "Custom"),
#"Added Conditional Column1" = Table.AddColumn(#"Expanded Custom", "Custom.1", each if [Recurring] = "Weekly" then [Custom] else [Date]),
#"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column1",{"Date", "Custom"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom.1", "Date"}})
in
#"Renamed Columns"
Result:
let
Source = #table({"Event","Recurring","Date"},{{"Event A","Weekly","9/22/2020"},{"Event B","No","8/14/2020"},{"Event C","No","3/15/2020"}}),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [Recurring] = "Weekly" then List.Generate(()=>[Date], each _ <= Date.From(DateTime.FixedLocalNow()), each _ + #duration(7,0,0,0)) else null),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Conditional Column", "Custom")
in
#"Expanded Custom"
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.
User | Count |
---|---|
107 | |
100 | |
80 | |
63 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |