Creating new rows and filling in blanks

[aspiringdaxking]

Relatively new to Power BI, so excuse any incorrect terminology.

I currently have a NATURALLEFTOUTERJOIN calculated table created that looks like below:

Pay Week Start	Pay Week End	Employee	Money Earned
10/2/2022	10/8/2022	Adam Smith	$20.00
10/9/2022	10/15/2022	Adam Smith	$20.00
10/16/2022	10/22/2022	Adam Smith	$20.00
10/2/2022	10/8/2022	Sam Kennedy	$25.00
10/16/2022	10/22/2022	Sam Kennedy	$25.00
10/23/2022	10/29/2022

But I need the table to look like below:

Pay Week Start	Pay Week End	Employee	Money Earned
10/2/2022	10/8/2022	Adam Smith	$20.00
10/9/2022	10/15/2022	Adam Smith	$20.00
10/16/2022	10/22/2022	Adam Smith	$20.00
10/23/2022	10/29/2022	Adam Smith	$0.00
10/2/2022	10/8/2022	Sam Kennedy	$25.00
10/9/2022	10/15/2022	Sam Kennedy	$0.00
10/16/2022	10/22/2022	Sam Kennedy	$25.00
10/23/2022	10/29/2022	Sam Kennedy	$0.00

To provide more context - the left table contains all of the "Pay Week Start" data, and the right table contains "Pay Week Start" as well as "Employee" and "Money Earned" data.

Sam Kennedy did not work between 10/9/2022 and 10/15/2022, and both Adam Smith and Sam Kennedy did not work between 10/23/2022 and 10/29/2022, and thus do not have any information in the right table. This is why 10/23/2022 is pulling in blanks for the "Employee" and "Money Earned" columns.

I am having difficulty figuring out how to create a new row for each "Employee", and then populating the "Money Earned" cell with a 0 if they did not work that week. The end goal is for all employees to have a row for each pay week, and for weeks not worked to have a 0. Any advice on how to approach?

[v-yueyunzh-msft · Accepted solution]

Hi, @aspiringdaxking 

 Here are the steps you can refer to :

(1)This is my test data :

(2)We need to create a 'Date' table like this:

(3)We can create a blank query and enter this M language:

let
    Source = Table.Group(Table1,"Employee",{"test",(x)=>Date }),
    #"Expanded test" = Table.ExpandTableColumn(Source, "test", {"Pay Week Start", "Pay Week End#(tab)"}, {"Pay Week Start", "Pay Week End"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded test",{{"Pay Week Start", type date}, {"Pay Week End", type date}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Pay Week Start", "Employee"}, Table1, {"Pay Week Start", "Employee"}, "Table1", JoinKind.LeftOuter),
    #"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries", "Table1", {"Money Earned"}, {"Money Earned"}),
    Custom1 = Table.TransformColumns(#"Expanded Table1" , {"Money Earned",(x)=>if x <> null then x else 0}    )
in
    Custom1

(4)The result is as follows:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[aspiringdaxking]

Just followed your steps and it mostly worked! But for some reason it's pulling in multiple Pay Week Starts for the same employee (duplicate rows essentially), and also no Pay Week Ends at all.

Looking into this further, but thought I'd give an update!

[v-yueyunzh-msft]

Hi , @aspiringdaxking 

Can you share the special data which lead to the error you said, so that wo can solve the problem with you.

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[aspiringdaxking]

Hey Aniya!

Just resolved the two problems:

1. Multiple Pay Week Starts were pulling in because my Date Table had each day, which resulted in multiple pay weeks being shown, so solution was to remove all the days.

2. Pay Week Ends not pulling in was due the "#(Tab)" part of the below line of code; removing it resolved my problem!

    #"Expanded test" = Table.ExpandTableColumn(Source, "test", {"Pay Week Start", "Pay Week End#(tab)"}, {"Pay Week Start", "Pay Week End"}),

Thank you so much for your help! Very easy to follow explanation and code!

[lbendlin]

Create a cross join between "all dates" and "all employees" and then map that to your fact table via a left join.

here's the crossjoin part.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTQN9I3MjAyUtIBsS1gbMeUxFyF4NzMkgwgR8XIQM/AQClWB6zeEkm9oSkRGgzNkHQYGRGhA6uTgoHKvVPz8lJTKsEaTAlbgU+LkTGyFrinQCg2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Pay Week Start" = _t, #"Pay Week End" = _t, Employee = _t, #"Money Earned" = _t]),
    Weeks = Table.Distinct(Table.SelectColumns(Source,{"Pay Week Start"}), {"Pay Week Start"}),
    #"Added Custom" = Table.AddColumn(Weeks, "Custom", each Table.Distinct(Table.SelectColumns(Source,{"Employee"}), {"Employee"})),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Employee"}, {"Employee"})
in
    #"Expanded Custom"

and here is the full version

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTQN9I3MjAyUtIBsS1gbMeUxFyF4NzMkgwgR8XIQM/AQClWB6zeEkm9oSkRGgzNkHQYGRGhA6uTgoHKvVPz8lJTKsEaTAlbgU+LkTGyFrinQCg2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Pay Week Start" = _t, #"Pay Week End" = _t, Employee = _t, #"Money Earned" = _t]),
    Weeks = Table.Distinct(Table.SelectColumns(Source,{"Pay Week Start"}), {"Pay Week Start"}),
    #"Added Custom" = Table.AddColumn(Weeks, "Custom", each Table.Distinct(Table.SelectColumns(Source,{"Employee"}), {"Employee"})),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Employee"}, {"Employee"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Money Earned", (k)=> try Table.SelectRows(Source,each k[Pay Week Start]=[Pay Week Start] and k[Employee]=[Employee]){0}[Money Earned] otherwise 0),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom1","",0,Replacer.ReplaceValue,{"Money Earned"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Money Earned", Currency.Type}})
in
    #"Changed Type"