Power BI text filter visual customization

[Anonymous]

Hi Team

Hope everyone is doing well!

I have been trying to modify text filter visual which is present in Power bi desktop. Output what i am looking is if i type any string in the serach box to serach a word and accordingly the dashboard should get filtered and parallely the search word should get stored in a csv file stored on teams. I tried modifying the code but line of code i am writing is not even reading the excel file for now on my local machine and throwing error, below are the details

Github location from where code is picked up: GitHub - microsoft/PowerBI-visuals-TextFilter: Text Filter PowerBI Visualization

Code modified in Perform search function: Have used infobox and infobox1 for debugging purpose, the code is not going inside wb.xlsx.readFile(fileName).then(function() and moving to catch with an error "TypeError: Cannot read properties of undefined (reading 'F_OK')"

const fileName = 'C:\\Users\\karishma.ahuja\\Downloads\\Word.xlsx';

      var Excel = require('exceljs');

      const wb = new Excel.Workbook();

      this.infoBox1.property("value",fileName);

      wb.xlsx.readFile(fileName).then(function()  

      {

      this.infoBox1.property("value","Readfile");

      var worksheet = wb.getWorksheet(1);

      this.infoBox1.property("value","Readworksheet");

      var lastRow = worksheet.lastRow;

      this.infoBox1.property("value",lastRow.toString());

      var getRowInsert = worksheet.getRow(++(lastRow.number));

      getRowInsert.getCell('A').value = 'yo';

      this.infoBox.property("value", getRowInsert);

      getRowInsert.commit();

      return wb.xlsx.writeFile(fileName);

      }

      ).catch((error) => {

      this.infoBox.property("value", JSON.stringify(error));

      this.infoBox.property("value", error);

      console.error("error");

      });

 Could someone help me with the code if i am doing anything wrong or is it something which is not compatible with power Bi ?     

[v-rongtiep-msft]

Hi @Anonymous ,

seems like the error is related to the file path. Please make sure that the file path is correct and that the file exists in the specified location. Also, make sure that the file is not open in any other program while you are trying to read or write to it.

If the file path is correct and the file exists, you can try using the module to check if the file exists before reading it. Here is an example:

const fs = require('fs');
const fileName = 'C:\\Users\\karishma.ahuja\\Downloads\\Word.xlsx';

if (fs.existsSync(fileName)) {
  var Excel = require('exceljs');
  const wb = new Excel.Workbook();
  this.infoBox1.property("value",fileName);
  wb.xlsx.readFile(fileName).then(function()  
  {
    this.infoBox1.property("value","Readfile");
    var worksheet = wb.getWorksheet(1);
    this.infoBox1.property("value","Readworksheet");
    var lastRow = worksheet.lastRow;
    this.infoBox1.property("value",lastRow.toString());
    var getRowInsert = worksheet.getRow(++(lastRow.number));
    getRowInsert.getCell('A').value = 'yo';
    this.infoBox.property("value", getRowInsert);
    getRowInsert.commit();
    return wb.xlsx.writeFile(fileName);
  }
  ).catch((error) => {
    this.infoBox.property("value", JSON.stringify(error));
    this.infoBox.property("value", error);
    console.error("error");
  });
} else {
  this.infoBox.property("value", "File not found");
}

This code checks if the file exists before reading it. If the file exists, it reads the file using the module. If the file does not exist, it logs a message to the property.

How to Get Your Question Answered Quickly 

If it does not help, please provide more details.

Best Regards
Community Support Team _ Rongtie

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

[Anonymous]

@v-rongtiep-msft  Above code didnt worked for me. What i understood after commenting line of code and validating is below line of code is not working in nodejs

const fs = require('fs');

Is there any replacement which i can try? Also,regarding the concern to check if file is open or not so, I  made sure while testing that the file is not open.