topic Re: Help with Lookup values and filling in blanks in dataset in DAX Commands and Tips

Help with Lookup values and filling in blanks in dataset

Anonymous — Wed, 09 Feb 2022 20:34:37 GMT

I am newer to Power Bi and I need help with on how to look up a value and if it exist in the dataset take a value and from that row and copy it into a blank value. For the example below i am trying to look up a material number that wasn't entered in that row however i have the document number of all line items and the Material number is the same for that document number. So i just want to lookup that document number and take the Material number and copy it to the blank spot that is missing a material number.

Re: Help with Lookup values and filling in blanks in dataset

Anonymous — Wed, 09 Feb 2022 21:00:34 GMT

Hello,

try this button

Re: Help with Lookup values and filling in blanks in dataset

Anonymous — Wed, 09 Feb 2022 21:22:56 GMT

The issue with using this option is it may fill incorrectly, there may not be another line item under that document number and if that's the case i need it to stay blank. I should have mentioned that in the post.

Re: Help with Lookup values and filling in blanks in dataset

V-lianl-msft — Mon, 14 Feb 2022 07:08:25 GMT

Hi Anonymous ,

The issue with using this option is it may fill incorrectly, there may not be another line item under that document number and if that's the case i need it to stay blank. I should have mentioned that in the post.

Sorry, could you describe this in more detail.show a picture to make us more clear what you expect.

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

Re: Help with Lookup values and filling in blanks in dataset

Anonymous — Tue, 15 Feb 2022 13:48:43 GMT

I was able to do this in excel using the formula below in the screen shot. However the auto fill does not always work, for example if you see the 2nd screen shot below you can see that sometimes a document number does not have an "MR number" assigned. If this is the case we need to leave it blank or this can cause errors in calucations . As you can see that document number 90058336 does not have an MR_Number anywhere so it would be blank but document number 90058322 has a blank so we need to assign it the same MR_Number that appears with with this document number from another "item of document".

Re: Help with Lookup values and filling in blanks in dataset

V-lianl-msft — Wed, 16 Feb 2022 07:41:02 GMT

Hi Anonymous ,

I use group by and remove duplication to get MR_ NUM of each DOC_NUM in the copy table, and then use the merge query.

Please refer to the sample pbix.

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