Monday, September 4, 2017

Expression.Error: The column 'xxxxxxxx' of the table wasn't found

Hello Everyone,

Yes, I'm on fire. Two posts in one day. :)

I'm not a Power Bi expert, that's why i don't want you to be like me, got lost for hours because of the error when trying to edit my query, I got the error message "Expression.Error: The column 'bla..bla..bla' of the table wasn't found" . This might be because you have renamed the header of your table, and because of that, power bi does not recognize it. Well, that was in my case :). Even after changing back the table header to the old name, still Power Bi did not like it. How to solve ?

Well, not exactly a great savvy high level programming way :).. In my case, i just wanted to redo..

  1. Rename the file source's table header the way you wanted it. So that you don't need to rename it on Power Bi.
  2. Make a new Power Bi file by using the source of the file that you have changed. (you just wanted to get the correct format of script)
  3. Open Data -> Edit Queries -> Advanced Editor
  4. Copy all line on  #"Changed Type"
  5. Open the Error Power Bi file, on Advanced Editor, replace the #"Changed Type" line with the one that you have copied.
  6. Replace "Renamed Columns" under "in" with "Changed Type" 
That makes it back to original one... Don't make anymore error :)




Incremantal Data Load Google Sheets

Hello Everyone !!!

Ya, you might think that this blog has lost focus. It's actually not. I found how to do the incremental data load using google sheets when i was in Saudi Arabia. So, it's still relevant :).. After dwelling around for hours on internet, i found the answer. This is how..

First of all, what is incremental data load ? This is one of examples. In case you have a list of employee and you need to record their attendance by using google sheets. This may look like this;


Okay, that's easy. Just need to put it down on the bottom of the last sheet. But, what if for some reasons, you already have the template on another google sheets, and the data need to be merged with above sheets, plus you want it to be placed on the bottom of the above sheet. Got it ?

1. On google sheets, go to Tools -> Script Editor
2. On Script Editor, paste below script:

function myFunction() {var ss = SpreadsheetApp.openById('XXXXXXX'); //replace with source ID
 var source = ss.getSheetByName('Sheet1'); //replace with source Sheet tab name
 var sourceRange = source.getRange('A2:O44'); //assign the range you want to copy
 var data = sourceRange.getValues();

 var tss = SpreadsheetApp.getActiveSpreadsheet('XXXXXX'); //replace with destination ID or keep it empty if you are running this script on destination google sheets.
 var ts = tss.getSheetByName('Sheet2'); //replace with destination Sheet tab name
 ts.getRange(ts.getLastRow()+1, 1, data.length, data[0].length).setValues(data);
 
}


3. Change the highlated yellow above with your Google sheets source and destination sheets.
4. Good Luck.. :)