Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Zepox
Frequent Visitor

Change value if column already has a value on given date.

Hello all,

I have a table which I pivoted to fill a name column with data for each date. The problem that I now have is empty rows of data on dates that already had data in the dataset. In image 1 below you can see how Peter and Pan already have data on 9-6-2022, but Jane does not. That is why I want to change the 0's from Peter and Pan to null so when I unpivot those will not turn into empty rows, where I want the 0 from Jane to stay so Jane will have a datapoint on 9-6-2022 with empty data. I am aware Jane will still get 2 rows of empty data but those I can simply remove by using the remove duplicate rows functionality. Below is my targeted situation (Image 2) where all red cells should contain null instead of 0.

I also included a result of the current unpivot for 9-6-2022 only to display my problem (Image 3).

 

Basically, what I want to do is something like if the name (Peter, Pan or Jane) already has a value for "Date", change 0 into null, otherwise keep the 0.

Zepox_0-1655794738800.png

Zepox_1-1655794969229.png

Zepox_2-1655795035404.png

 

 

1 ACCEPTED SOLUTION

Create query with name Users

let
    Source = {"Peter", "Pan", "Jane"},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Dates", each List.Numbers(44721,20)),
    #"Expanded Dates" = Table.ExpandListColumn(#"Added Custom", "Dates"),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Dates",{{"Dates", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Name"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Name", type text}})
in
    #"Changed Type1"

Create secon query with any name

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"FullDateList"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Name"}}),
    Custom1 = Users,
    #"Merged Queries" = Table.NestedJoin(Custom1, {"Name", "Dates"}, Table1, {"Attribute", "FullDateList"}, "Table1", JoinKind.LeftOuter),
    #"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries", "Table1", {"Value"}, {"Value"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Table1",{{"Dates", Order.Ascending}, {"Name", Order.Ascending}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Sorted Rows",{"Dates", "Name", "Value"})
in
    #"Reordered Columns"

Second query merges query Users and shows data for every name and every date.

 

Second solution from my first post would be something like this

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"FullDateList", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"FullDateList"}, {{"Peter", each List.RemoveItems([Peter],{0,null}), type any}, {"Pan", each List.RemoveItems([Pan],{0,null}), type any}, {"Jane", each List.RemoveItems([Jane],{0,null}), type any}}),
    #"Expanded Jane" = Table.ExpandListColumn(#"Grouped Rows", "Jane"),
    #"Expanded Pan" = Table.ExpandListColumn(#"Expanded Jane", "Pan"),
    #"Expanded Peter" = Table.ExpandListColumn(#"Expanded Pan", "Peter"),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Peter",null,"0",Replacer.ReplaceValue,{"Peter", "Pan", "Jane"}),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Replaced Value", {"Peter", "Pan", "Jane"}, "Attribute", "Value")
in
    #"Unpivoted Only Selected Columns"

 

I woul got with the solution with two queries because you can find what is minimum and maximum dates in your input data and find unique names in the source data and automate fully the query preparation of basic data in query Users.

I hope it will help you create your solution.

 

Artur

View solution in original post

7 REPLIES 7
artpil
Resolver II
Resolver II

Hi,

I'm not sure wat do you want to achive but I fought of using group by statement

=Table.Group(Source, {"FullDateList"}, {{"Peter", each List.RemoveItems([Peter],{0,null}), type any}, {"Pan", each List.RemoveItems([Pan],{0,null}), type any}, {"Jane", each List.RemoveItems([Jane],{0,null}), type any}})

 where instead of creating groupped columns you can create lists of values from each column and remove from the lsts values 0 and null. After expanding the columns you will get some cells with null value which can be replaced by zero. After unpivoting you will get the value for each name for each date.

artpil_1-1655799218490.png

Artur

You can obtain the same effect by creating a table of all names and dates.

artpil_1-1655800667447.png

Then expand column dates to new rows and change data type to date. Then you can merge this table with previously unpivoted data where all zeros can be changed to null because after merge of two tables (left merge from dates table with unpivoted table) will give you the output with all names for all dates with data in value column if exists or null if not exists.

Artur

Zepox
Frequent Visitor

I'm sorry I don't think I can follow your solution. I can create the table with names and each name having all the corresponding dates. However after I merge with the unpivoted data I don't seem to get it right because whatever I do I end up with the same situation as before, I will either have rows with 0's despite there already being data or not every name will have a row for each date in the dataset.

Create query with name Users

let
    Source = {"Peter", "Pan", "Jane"},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Dates", each List.Numbers(44721,20)),
    #"Expanded Dates" = Table.ExpandListColumn(#"Added Custom", "Dates"),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Dates",{{"Dates", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Name"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Name", type text}})
in
    #"Changed Type1"

Create secon query with any name

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"FullDateList"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Name"}}),
    Custom1 = Users,
    #"Merged Queries" = Table.NestedJoin(Custom1, {"Name", "Dates"}, Table1, {"Attribute", "FullDateList"}, "Table1", JoinKind.LeftOuter),
    #"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries", "Table1", {"Value"}, {"Value"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Table1",{{"Dates", Order.Ascending}, {"Name", Order.Ascending}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Sorted Rows",{"Dates", "Name", "Value"})
in
    #"Reordered Columns"

Second query merges query Users and shows data for every name and every date.

 

Second solution from my first post would be something like this

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"FullDateList", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"FullDateList"}, {{"Peter", each List.RemoveItems([Peter],{0,null}), type any}, {"Pan", each List.RemoveItems([Pan],{0,null}), type any}, {"Jane", each List.RemoveItems([Jane],{0,null}), type any}}),
    #"Expanded Jane" = Table.ExpandListColumn(#"Grouped Rows", "Jane"),
    #"Expanded Pan" = Table.ExpandListColumn(#"Expanded Jane", "Pan"),
    #"Expanded Peter" = Table.ExpandListColumn(#"Expanded Pan", "Peter"),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Peter",null,"0",Replacer.ReplaceValue,{"Peter", "Pan", "Jane"}),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Replaced Value", {"Peter", "Pan", "Jane"}, "Attribute", "Value")
in
    #"Unpivoted Only Selected Columns"

 

I woul got with the solution with two queries because you can find what is minimum and maximum dates in your input data and find unique names in the source data and automate fully the query preparation of basic data in query Users.

I hope it will help you create your solution.

 

Artur

Zepox
Frequent Visitor

Amazing, thank you for the detailed response. It fully works as intended now.

Edit: Loading time with your solution is also only a third from what it was with mine, thanks again.

rohit_singh
Solution Sage
Solution Sage

Hi @Zepox ,

Please try this :

In power query, select columns Peter and Pan in your table, and click on Replace Values. 

rohit_singh_0-1655798044714.png

Replace 0 with null and unpivot columns Peter, Pan and Jane.

rohit_singh_1-1655798116950.png

The extra rows for Peter and Pan will be removed and the 0's for Jane will be kept.

 

Kind regards,

Rohit


Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! :}

Hello,

 

This wouldn't really work as days where Peter or Pan don't have data, would also get removed. These should still be  a datapoint just like Jane where it will be just the date, name and thee 0 for the other column(s).  Also this way Jane would have empty rows for dates that already have data.

 

Regards,

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors