cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
twister8889
Helper V
Helper V

Convert null dates to all dates existing into table

Hi guys,

 

I have a table that contains User and Date columns for example, and the dates can be null because I am doing a left join for users table.
I would like that when the user doesn't have a date, replace null for all existing values from another user.

How do I do this? is it in power query or dax?

Original table

UserDate
A01/01/2021
A02/01/2021
C03/01/2021
Bnull

Expected table

UserDate
A01/01/2021
A02/01/2021
C03/01/2021
B01/01/2021
B02/01/2021
B03/01/2021

 

1 ACCEPTED SOLUTION
PaulDBrown
Super User
Super User

Try this in a new table:

 

 

New Table =
VAR _Dates =
    DISTINCT ( OriginalTable[Date] )
VAR _BlankVal =
    CALCULATETABLE (
        VALUES ( OriginalTable[User] ),
        FILTER ( OriginalTable, ISBLANK ( OriginalTable[Date] ) )
    )
VAR _AssignDates =
    FILTER ( CROSSJOIN ( _BlankVal, _Dates ), NOT ( ISBLANK ( [Date] ) ) )
VAR _OrigOK =
    FILTER ( OriginalTable, NOT ( ISBLANK ( OriginalTable[Date] ) ) )
RETURN
    UNION ( _OrigOK, _AssignDates )

 

 

NewTable Dates.JPG

 

Or this in Power Query (you will need to use your source table code for Source and Source1 lines)

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIw1AciIwMjQ6VYHaiQEYqQM0jIGEXICSikFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [User = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"User", type text}, {"Date", type date}}),
    #"GoodRows" = Table.SelectRows(#"Changed Type", each ([Date] <> null)),
    Source1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIw1AciIwMjQ6VYHaiQEYqQM0jIGEXICSikFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [User = _t, Date = _t]),
    #"Changed Type1" = Table.TransformColumnTypes(Source1,{{"User", type text}, {"Date", type date}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([Date] = null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Date"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each #"GoodRows"),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Date"}, {"Custom.Date"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Custom",{{"Custom.Date", "Date"}}),
    #"Appended Query" = Table.Combine({#"GoodRows", #"Renamed Columns"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Appended Query",{{"Date", type date}})
    
in
    #"Changed Type2"

 

AppendedDates.JPG

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

2 REPLIES 2
PaulDBrown
Super User
Super User

Try this in a new table:

 

 

New Table =
VAR _Dates =
    DISTINCT ( OriginalTable[Date] )
VAR _BlankVal =
    CALCULATETABLE (
        VALUES ( OriginalTable[User] ),
        FILTER ( OriginalTable, ISBLANK ( OriginalTable[Date] ) )
    )
VAR _AssignDates =
    FILTER ( CROSSJOIN ( _BlankVal, _Dates ), NOT ( ISBLANK ( [Date] ) ) )
VAR _OrigOK =
    FILTER ( OriginalTable, NOT ( ISBLANK ( OriginalTable[Date] ) ) )
RETURN
    UNION ( _OrigOK, _AssignDates )

 

 

NewTable Dates.JPG

 

Or this in Power Query (you will need to use your source table code for Source and Source1 lines)

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIw1AciIwMjQ6VYHaiQEYqQM0jIGEXICSikFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [User = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"User", type text}, {"Date", type date}}),
    #"GoodRows" = Table.SelectRows(#"Changed Type", each ([Date] <> null)),
    Source1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIw1AciIwMjQ6VYHaiQEYqQM0jIGEXICSikFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [User = _t, Date = _t]),
    #"Changed Type1" = Table.TransformColumnTypes(Source1,{{"User", type text}, {"Date", type date}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([Date] = null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Date"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each #"GoodRows"),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Date"}, {"Custom.Date"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Custom",{{"Custom.Date", "Date"}}),
    #"Appended Query" = Table.Combine({#"GoodRows", #"Renamed Columns"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Appended Query",{{"Date", type date}})
    
in
    #"Changed Type2"

 

AppendedDates.JPG

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

First of all, thank you for your answer..

I used the power query version (a little bit slow to refresh the data because I have data for all days, like 365 lines by user) , I just removed the duplicated values and perfect result.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.