Replace null with relative cell reference

[Anonymous]

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.

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

Thank you VERY much for help and assistance!!

[Anonymous]

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!

[Anonymous]

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]

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

--Nate. 

[Anonymous]

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,

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

[watkinnc · Accepted solution]

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

[watkinnc]

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]

Hello @Anonymous 

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"

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

[Anonymous]

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]

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.

[ziying35]

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]

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

[Anonymous]

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

[Anonymous]

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!