The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
hello,
I have a table that looks like this:
Period | Scenario | Department | Revenue |
17-Dec | Actual | department1 | 500 |
18-Mar | Forecast | department1 | 550 |
18-Jun | Forecast | department1 | 600 |
18-Sep | Forecast | department1 | 500 |
18-Dec | Forecast | department1 | 530 |
18-Dec | Actual | department1 | 535 |
19-Mar | Forecast | department1 | 580 |
19-Jun | Forecast | department1 | 600 |
19-Sep | Forecast | department1 | 620 |
19-Dec | Forecast | department1 | 590 |
19-Dec | Actual | department1 | 600 |
20-Mar | Forecast | department1 | 620 |
20-Jun | Forecast | department1 | 630 |
20-Sep | Forecast | department1 | 620 |
20-Dec | Forecast | department1 | 610 |
20-Dec | Actual | department1 | 600 |
17-Dec | Actual | department2 | 575 |
18-Mar | Forecast | department2 | 633 |
18-Jun | Forecast | department2 | 690 |
18-Sep | Forecast | department2 | 575 |
18-Dec | Forecast | department2 | 610 |
18-Dec | Actual | department2 | 615 |
19-Mar | Forecast | department2 | 667 |
19-Jun | Forecast | department2 | 690 |
19-Sep | Forecast | department2 | 713 |
19-Dec | Forecast | department2 | 679 |
19-Dec | Actual | department2 | 690 |
20-Mar | Forecast | department2 | 713 |
20-Jun | Forecast | department2 | 725 |
20-Sep | Forecast | department2 | 713 |
20-Dec | Forecast | department2 | 702 |
20-Dec | Actual | department2 | 700 |
19-Dec | Actual | department 3 | 345 |
20-Mar | Forecast | department 3 | 357 |
20-Jun | Forecast | department 3 | 362 |
20-Sep | Forecast | department 3 | 357 |
20-Dec | Forecast | department 3 | 351 |
20-Dec | Actual | department 3 | 350 |
Department 3 Doesn't have any records prior to 2020.
Would it be possilbe to do the following in Power query: for each department for each year where the recod where scenario "Actual" doesn't exist -insert a row with revenue equal to 0.
I am looking for this output- inserted two rows with 0s for dep 3
Period | Scenario | Department | Revenue |
17-Dec | Actual | department1 | 500 |
18-Mar | Forecast | department1 | 550 |
18-Jun | Forecast | department1 | 600 |
18-Sep | Forecast | department1 | 500 |
18-Dec | Forecast | department1 | 530 |
18-Dec | Actual | department1 | 535 |
19-Mar | Forecast | department1 | 580 |
19-Jun | Forecast | department1 | 600 |
19-Sep | Forecast | department1 | 620 |
19-Dec | Forecast | department1 | 590 |
19-Dec | Actual | department1 | 600 |
20-Mar | Forecast | department1 | 620 |
20-Jun | Forecast | department1 | 630 |
20-Sep | Forecast | department1 | 620 |
20-Dec | Forecast | department1 | 610 |
20-Dec | Actual | department1 | 600 |
17-Dec | Actual | department2 | 575 |
18-Mar | Forecast | department2 | 633 |
18-Jun | Forecast | department2 | 690 |
18-Sep | Forecast | department2 | 575 |
18-Dec | Forecast | department2 | 610 |
18-Dec | Actual | department2 | 615 |
19-Mar | Forecast | department2 | 667 |
19-Jun | Forecast | department2 | 690 |
19-Sep | Forecast | department2 | 713 |
19-Dec | Forecast | department2 | 679 |
19-Dec | Actual | department2 | 690 |
20-Mar | Forecast | department2 | 713 |
20-Jun | Forecast | department2 | 725 |
20-Sep | Forecast | department2 | 713 |
20-Dec | Forecast | department2 | 702 |
20-Dec | Actual | department2 | 700 |
17-Dec | Actual | department 3 | 0 |
18-Dec | Actual | department 3 | 0 |
19-Dec | Actual | department 3 | 345 |
20-Mar | Forecast | department 3 | 357 |
20-Jun | Forecast | department 3 | 362 |
20-Sep | Forecast | department 3 | 357 |
20-Dec | Forecast | department 3 | 351 |
20-Dec | Actual | department 3 | 350 |
Thank you!
Solved! Go to Solution.
Hello @ogend
first of all in your period-column the year is missing. And there are also missing some specification like which period to assign to the new row etc. What I tried to do is to add a new column that extracts the year. Then I grouped by Year and Deparment adding a function that takes the whole table. Then I added a new column where I checked if I can find the word "Actual" in the scenario-column. If no, I added a row, using the data from the grouped table. This means that you need to have a record in that year but not containing "Actual". If so, I added it using the first period found of this year. Hope you can understand my approach. Here the code of my solution
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrTUMzDUMzIwtFTSUXLLL0pNTiwuATJTUgsSi0pyU/NKDIE8U1MDpVgdhGojA6CgY3JJaWIOploDLGopNtmIJJONYCbHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Period = _t, Scenario = _t, Department = _t, Revenue = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Period", type date}, {"Scenario", type text}, {"Department", type text}, {"Revenue", Int64.Type}}, "de-DE"),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Period]), type number),
#"Grouped Rows" = Table.Group(#"Added Custom", {"Department", "Year"}, {{"AllRows", each _, type table [Period=date, Scenario=text, Department=text, Revenue=number, Year=number]}}),
#"Added Custom1" = Table.AddColumn(#"Grouped Rows", "HasActual", each if List.Contains([AllRows][Scenario],"Actual") then [AllRows] else Table.Combine({[AllRows], #table({"Department", "Year", "Period", "Scenario", "Revenue"},{{[Department], [Year], [AllRows][Period]{0}, "Actual", 0}})})),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"AllRows"}),
#"Expanded HasActual" = Table.ExpandTableColumn(#"Removed Columns", "HasActual", {"Period", "Scenario", "Revenue"}, {"Period", "Scenario", "Revenue"})
in
#"Expanded HasActual"
this transforms this
into this
adding in 2019 for dept1 a new row with 0 for actual using the 19.01.19 as period.
Copy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
Hello @Jimmy801 ,
Thanks a lot fot your help!
I attempted to tweak your code to make it work with my Excel source file, but unfortunately it does not populate 0 line rows
would you be able to to take a look?
let
Source = Excel.Workbook(File.Contents("C:\Users\Olga\Sample.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Period", type date}, {"Scenario", type text}, {"Department", type text}, {"Revenue", Int64.Type}}),
// #"Changed Type" = Table.TransformColumnTypes(Source,{{"Period", type date}, {"Scenario", type text}, {"Department", type text}, {"Revenue", Int64.Type}}, "de-DE"),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Period]), type number),
#"Grouped Rows" = Table.Group(#"Added Custom", {"Department", "Year"}, {{"AllRows", each _, type table [Period=date, Scenario=text, Department=text, Revenue=number, Year=number]}}),
#"Added Custom1" = Table.AddColumn(#"Grouped Rows", "HasActual", each if List.Contains([AllRows][Scenario],"Actual") then [AllRows] else Table.Combine({[AllRows], #table({"Department", "Year", "Period", "Scenario", "Revenue"},{{[Department], [Year], [AllRows][Period]{0}, "Actual", 0}})})),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"AllRows"}),
#"Expanded HasActual" = Table.ExpandTableColumn(#"Removed Columns", "HasActual", {"Period", "Scenario", "Revenue"}, {"Period", "Scenario", "Revenue"})
in
#"Expanded HasActual"
// #"Changed Type"
Hello @ogend
I cannot see any problems in the code. Are there some error messages? What is your final step showing? (Empty table or error). Could you make me please a screenshot of the data after the "Changed Type"-step. Did you check that your date-column is containing also a year? Could you share in case your sampel.xlsx?
BR
Jimmy
Hello @ogend
first of all in your period-column the year is missing. And there are also missing some specification like which period to assign to the new row etc. What I tried to do is to add a new column that extracts the year. Then I grouped by Year and Deparment adding a function that takes the whole table. Then I added a new column where I checked if I can find the word "Actual" in the scenario-column. If no, I added a row, using the data from the grouped table. This means that you need to have a record in that year but not containing "Actual". If so, I added it using the first period found of this year. Hope you can understand my approach. Here the code of my solution
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrTUMzDUMzIwtFTSUXLLL0pNTiwuATJTUgsSi0pyU/NKDIE8U1MDpVgdhGojA6CgY3JJaWIOploDLGopNtmIJJONYCbHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Period = _t, Scenario = _t, Department = _t, Revenue = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Period", type date}, {"Scenario", type text}, {"Department", type text}, {"Revenue", Int64.Type}}, "de-DE"),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Period]), type number),
#"Grouped Rows" = Table.Group(#"Added Custom", {"Department", "Year"}, {{"AllRows", each _, type table [Period=date, Scenario=text, Department=text, Revenue=number, Year=number]}}),
#"Added Custom1" = Table.AddColumn(#"Grouped Rows", "HasActual", each if List.Contains([AllRows][Scenario],"Actual") then [AllRows] else Table.Combine({[AllRows], #table({"Department", "Year", "Period", "Scenario", "Revenue"},{{[Department], [Year], [AllRows][Period]{0}, "Actual", 0}})})),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"AllRows"}),
#"Expanded HasActual" = Table.ExpandTableColumn(#"Removed Columns", "HasActual", {"Period", "Scenario", "Revenue"}, {"Period", "Scenario", "Revenue"})
in
#"Expanded HasActual"
this transforms this
into this
adding in 2019 for dept1 a new row with 0 for actual using the 19.01.19 as period.
Copy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
There may be better ways to do it but one the ones ways if:
If you have a continuous date table in Power Query having dates from the beinginning till the end, you can make a join of right outer join, the table above being on the left and date table on the right.
This will give you the missing dates in your table and null values.
You can replace null values according to your requirement
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
39 | |
21 | |
19 | |
15 | |
13 |