cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Dinah
Frequent Visitor

Replace null with relative cell reference

Hi,

I am attemping to use Power Query to manipulate a downloaded report. The report puts data for a single invoice on multiple lines, I would like to reconfigure all relevant data onto a single line. I have done this in excel by adding a column and writing an IF statement which recognizes a change in Invoice Number and pulls the the corresponding Invoice Date, then by filtering duplicated data I am left with usable information on a single line. Can the same type of process be replicated in Power Query?

Below is a snip of the table data.

Dinah_1-1606847546225.png

 

Please let me know if there is something further I can provide.

 

Thank you VERY much for help and assistance!!

 

 

1 ACCEPTED SOLUTION
watkinnc
Solution Specialist
Solution Specialist

IN that case, why not Use the FillDown function on the Invoice Date column, thus filling in the nulls with the date above.  Then you can filter out the nulls in the Product/Item Description.

---Nate

View solution in original post

13 REPLIES 13
Dinah
Frequent Visitor

Woo-Hoo! Thank you Nate. I think that will work fairly well!!

 

I was able to fill up or down based on whether the first cell was "null" and because the data repeats evenly this was a very efficient solution!

 

Thanks again to all for help!

*<]:0) Happy Holidays to All!

 

Dinah
Frequent Visitor

Thank to All who assisting with this project. I am new to Power Query/BI, so I am slow in moving through the recommendations and am very grateful for the infomration.

 

I hope the below link works. It is a desensitized spreadsheet with two tabs. The first is the download report, the second is the configured report.

 

https://www.dropbox.com/s/hkw2kacult8tdpc/Report%20Downloads.xlsx?dl=0

 

Please let me know if I can present this better. Thank you again!

watkinnc
Solution Specialist
Solution Specialist

In this case, you could even right click on the nulls in the date column, and click Filter-Does not Equal.

--Nate. 

Dinah
Frequent Visitor

Thank you Nate,

I am not certain the Filter by Does not Equal or Remove Duplicates is a solution for this issue because there is data in other columns that is essential, see below. So if I filter to remove the "null" from one column it eliminates the pertinent data in the other column,

Dinah_0-1606933586455.png

In this example I need the INV # WW2020ZL593, 9/9/20, Willow Park - Maya 20-Piece Flatware Set all on one line.

watkinnc
Solution Specialist
Solution Specialist

IN that case, why not Use the FillDown function on the Invoice Date column, thus filling in the nulls with the date above.  Then you can filter out the nulls in the Product/Item Description.

---Nate

View solution in original post

watkinnc
Solution Specialist
Solution Specialist

You can simply use the FillDown function in the Graphical User Interface (select the date column first).

Then Table.Distinct(PriorStepName) or just select both columns and click "Remove Duplicates".

That's it!--Nate

Jimmy801
Super User III
Super User III

Hello @Dinah 

 

Use Table.Group and apply different function to aggregate the data you need of the grouped table. I made an example with the data you provided. For the invoice-column it takes the first value that is other than null. Add new functions for all other columns you want to aggregate.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTK00DMw1DMyUIrVgQjgYDgBGUamegZGMLVOyDJIjFgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Invoice Number" = _t, #"Invoice Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Invoice Number", type text}, {"Invoice Date", type date}}, "de-DE"),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Invoice Number"}, {{"Invoice Date", each List.Select( _[Invoice Date], each _ <> null){0}, type date}})
in
    #"Grouped Rows"

 

Jimmy801_0-1606889309555.png

 

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

Dinah
Frequent Visitor

Wow Jimmy801! I have no shame in admitting you skill is beyond my own; which is no doubt why you are the Super User! 😉 

I used your cut&paste, without the faintest idea (I viewed in advanced editor as directed). As noted in your screen shot a Custom Invoice Numer Column was created and a Custom Invoice Date Column was created with the dates of 1/18/20 and 2/25/20 created for each Custom Invoice A and B, respectively.

 

Because I do not "understand" the code that well, I am having a difficult time manipulating it to arrive at the intended results.
I appreciate your thoughts, but am also aware that my ability combined with complexity of the task may be too large for this forum. Thank you!


edhans
Super User III
Super User III

Probably, but I'm not clear on your ask. Please provide sample data via a table or shared file from OneDrive, Dropbox, etc.

 

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
ziying35
Impactful Individual
Impactful Individual

You can upload the file containing the desensitized source data and the desired result to the cloud drive, and then share the link here.

mahoneypat
Super User IV
Super User IV

Example data would be good, but have you explored the Group By function in the ribbon?  It could be used to get to a single row for each invoice, aggregating one of the values from each column.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Rocco_sprmnt21
Super User II
Super User II

if you share you excel file, many peoples here can give you what you are after.

I am sorry to be a pest - but when I attempt to drag my sample WB, I get the error: "The file type () is not supported"

I have tried xlsm, pbix and others. I have been looking for general information about forum uploads, but I have not located information to assist. Thanks for your help!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors