Excel Power Query: Concatenating a Multiple-Value List source inside a single cell.

Excel Power Query is an awesome tool to get all kinds of things done .
One can draw in information from anywhere, from Excel tables, csv files, Sharepoint connections or other databases.

Once the information is in the document, one can perform several filters and transformations before the data is actually placed in a table inside the document.

In my case I am working with Sharepoint List sources.

Not all content is displayed verbatim when it comes from Sharepoint. Lists for instance, are simply displayed as [List] inside the Excel's cell.

If your list is a multiple-value one, you may expand it, but you will then multiply the number of rows, which is like a cartesian product.

You may want to present it differently though. Like me, you may want to display the list as a comma-separated value inside the cell.

Turns out Excel Power Query is pretty powerful and there is a way to do that.

A column can be transformed in a lot of ways. This sequence below is buta dab in the water of Power  Query, and may help you people out there.

If yout List is text-based it's a simpler matter:

let

    Source = ...#"your source"...,
    #"transform" = Table.TransformColumns( #"your source" , {"list column", each Text.Combine( _ ,",")})

in
    #"transform"

If your list (column-type) is number-based or a lookup to another Sharepoint List though, it's a bit more involved:

let

    Source = ...#"your source"...,
    #"transform" = Table.TransformColumns( #"your source" , {"list column", each Text.Combine( List.Transform( _ , each Number.ToText(_) ),",")})

in
    #"transform"

So what do we do here?

Table.TransformColumns - This picks up our source column and drill down on each record, performing a transformation function on each.

Text.Combine - this one concatenates a series of text strings from a list.

List.Transform - This performs a transformation for a numeric list (and lookup lists) to become a text list.

Number.ToText - This is applied on each element of the List to make it parsable by Text.Combine.



For the full reference:

Power Query Formula Reference:

https://msdn.microsoft.com/en-us/library/mt211003.aspx

Adobe PDFMaker COM supplement makes Office 2013 behave wonky in network operations

I have stumbled in this weird problem.

Some users, in some files, seemed to be unable to use 'Save As...' in word 2013 in some network files.
The Save As dialog was not appearing, no amount of keyboard bashing or button clicking would solve that.

I checked the network server permissions and they were good. Stumped, I started debugging by disabling the COM supplements one by one.

And I had this eureka moment when I disabled Adobe's extension and network file operations started working again.

Microsoft's native impementation may not make files as small as Adobe's, but it is in reasonably good shape, it has been working well since Word 2010.

Google Drive for PC bug breaks Windows Search (and how to fix it)

Some of you folks like me, which use Google Drive for PC to sync your files with the cloud, may have noticed that Windows Search does not return results from files inside the Google Drive folder.

It's not Microsoft's fault though. The problem is that the Google Drive for PC sync service is not honoring the permission inheritance from its parent folder.

The SYSTEM permissions are not being propagated to files synced by Google Drive. This causes the Windows Search index to not have access privileges to the root Google Drive folder and all its contents.

There is no fix per se (that should come from Google), but there is a workaround.

UPDATE: It turns out you can solve the search issue in a relatively simple way, by just enabling inheritance at Google Drive's root folder.

To accomplish that you can do as follows:

1. Open Windows Explorer.
2. Go to your user's root folder.
3. At the right panel, right-click on Google Drive's root folder and click on 'Properties'.
4. Click on the 'Security' tab.
5. Click on the 'Advanced' button.
6. A new window will show up, it should be named similar to 'Advanced Security Settings for Google Drive'.
7. At the bottom of this window, there is a button and a checkbox.
8. Click on the 'Enable Inheritance' button.
9. Check the 'Replace all child object permission entries with inheritable permission entries from this object'.
10. Click 'Apply'.
11. Click 'OK' to close this window.
12. Click 'OK' again to dismiss the Properties window.
13. Done.

Windows Search should start indexing the contents as soon as the permissions are changed.

nano-nugget of wisdom: ping with timestamp

On your Linux/Mac terminal, just type or paste:

ping google.com | while read pong; do echo "$(date): $pong"; done
And voila.

Chega (não é só pelos 20 centavos)

A quase ausência de regulamentação sobre a mídia é uma faca de dois gumes, pois ao mesmo tempo que pode ser uma garantia da liberdade de comunicação, também já era um jogo de cartas marcadas desde a ditadura, com poucas famílias mantendo uma hegemonia de direita editorando e manipulando a informação que é impingida à grande massa.

Mas hoje temos novas formas, mais ágeis de nos comunicar, temos este fantástico veículo da informação digital denominado internet, que transforma fundamentalmente o valor da informação, dados que temos a capacidade de difundir e reproduzir a informação em nível mundial a um custo próximo de zero.

Com este meio à disposição, os menestréis, trovadores, poetas, cantores e artistas que sempre tiveram um papel importante nas transformações sociais, absorvendo, decantando, cristalizando e refletindo o espírito, os anseios que quem busca uma mudança para melhor, podem se expressar deixando de lado a velha mídia, trazendo sua mensagem e inspiração para o povo do qual fazem parte:

How to enable raw disk access for VMWare Workstation on Windows 8

If you are trying to run a virtual machine from a physical disk on VMWare Workstation, chances are you will be denied raw disk access with an error message.

The only way I managed to allow VMWare to access the raw partition was to use diskpart.

diskpart is a command-line app which requires administrator privileges.

You will be better off if you take is slow and read diskpart documentation, as it can wipe out your disk partition table. Keep your backups up-to-date. You've been warned.

The process was this:

Start a command line, then type 'diskpart' and press enter.

If you run diskpart from a command line without administrator privileges, you will be prompted for the admin password, and diskpart will run on a separate window.

type 'list disk' and press enter. You should see all disks and will be able to verify which one you want to enable raw disk access.

use the command 'select disk N' where N is the number of the disk.

use the command 'offline' to keep windows disk management's grubby hands out of it.

use the command 'attributes disk clear readonly' to allow the disk to be written to.

And voilá, that should do it. YMMV.

Installing Boot Camp using Parallels

http://insidethebrackets.blogspot.com.br/2009/04/install-windows-on-macbook-air-with-no.html

Changing default settings of (xerox) printers on Mac OS X

So, I have this new awesome waxjet printer in my office, a Xerox ColorCube 8570.

Problem is, in Mac there is no way to alter the 'Default Settings' of the printer... at least in an accessible way for mere mortals.

But there's always some hidden magic for geeks to wiz around and do the impossible.

Mac OS X uses CUPS behind the scenes, so we can just open a browser and type the URL:

http://127.0.0.1:631

If you get an 'access denied' or 'web interface disabled' error, open a Terminal and type the following:

cupsctl WebInterface=yes

And press enter; reload the above link.

You should then see a CUPS configuration interface where you can change the printer default settings to your heart's contents.

This interface is not restricted to xerox printers as far as I know. But since I don't have non-Xerox printers I cannot test it.

Configurando tronco Vono no Elastix (Asterisk, FreePBX)

Depois de um pouco de sangue, suor e lágrimas, descobri como configurar um tronco Vono no Elastix (Asterisk, FreePBX).

Aí vai a fórmula:

TRUNK NAME = vono_out (como quiser chamar)

PEER Details

type=peer
username=
secret=
domain=vono.net.br
fromuser=
fromdomain=vono.net.br
host=vono.net.br
insecure=very
context=from-pstn
qualify=yes
port=5060
aut=md5
nat=yes
dynamic=yes
disallow=all
allow=g729;ilbc;gsm;ulaw;alaw;

dtmfmode=rfc2833
reinvite=no
canreinvite=no

--------------------------------

USER CONTEXT = vono_in (como quiser chamar)

USER Details
type=user
username=
domain=vono.net.br
fromuser=
fromdomain=vono.net.br
host=vono.net.br
insecure=very
context=from-pstn
disallow=all
allow=g729;ilbc;gsm;ulaw;alaw;

----------------------------

REGISTER STRING

:@vono.net.br:5060/

jQuery UI List widget released!

I have built this widget for a company project with modularity in mind, and thought it would be a good idea to share with the world.

Read more about it here or download it here.

jQuery More Support Plugin released!

This plugin extends the jQuery.support property with a few more variables for the developer's convenience. Currently it adds the following:

a) Detects the existence of the console.
b) Detects the browser engine by feature-detection rather than user-agent sniffing.
b.1) Detects Internet Explorer version. Not implemented for the other browsers, may or may not be possible.

Read more about it here or get it here.

jQuery UUID Generator updated!

To all jQuery jockeys ou there:

I have managed to release an update for my UUID generator plugin for jQuery. It has new features, and a new function name as well.

Read more about it here, or go get it here.

Considerations on Mozilla's IndexedDB

I have read Mozilla's proposal for IndexedDB, and while I am no defender of SQLite on the client-side, I also disliked a lot the proposed syntax.

It is just too verbose and convoluted to do a simple thing as a join, as their example showed. It requires 2~3 more code than their own WebDatabase counter-example, and is hard to wrap your brain around the usage.

I would prefer a document-based storage method which can simply save JS objects to disk, and is able to fetch them back or operate with them, similar to couchDB. The documents would all be required to have an id (primary key in SQL terms).

I will build those examples on top of the kids/candy database used by the mozilla blog post.

var db = indexedDB.open("somedb"); //sets the db for reading 


db.async = true|false // sets (a)synchronous execution
db.autosave = true|false // if set to true, there is no need to call the save() function to commit the changes to the storage, as they happen automatically.


var onekid = db.filter(id="id"); // prepares a single document to be fetched from the storage 
onekid.get(); // fetches the onekid data as a JS object.
onekid.age = 12; // adds or changes some information on the document
onekid.save(); // saves to the storage
OR we could do:
onekid.save().onsuccess = function(event){
    alert("saved "+event.objects[0]+" successfully."); // the objects property of the event would store all ids of all documents affected by the transaction.
});


db.add({name:"john"}); // add an object to the storage
db.save();


var kidstable = db.filter({"name":"john","age":"36"}) // prepares to fetch multiple property+value pairs
kidstable = db.filter(["name","age"]) // prepares to fetch multiple properties
kidstable = db.filter("name") // prepares to fetch all documents with a name property
kidslist = kidstable.get(); //this would happen synchronously and return the actual documents as JS objects to be assigned to kidslist


kidstable.set("gender"); /* sets a gender property on all documents represented by the filter */
kidstable.set.onfailure = function(event){alert("could not set property "+event.key);}
kidstable.set({gender:"male"}); /* sets the gender property to value 'male' on all documents represented by kidstable */


result = kidstable.set({age:12}); /* forces synchronous execution. returns true on success, false on failure */
data = kidstable.set({age:10}).get(); /* sets the data, then gets the modified data; */
kidstable.set({age:10,gender:"female"}); /* multiple properties at once */
kidstable.unset(["gender","age"]); /*removes properties. */


kidstable.del(); //deletes objects


set, unset and del functions could happen on memory-space and vaporize if save() is not called (unless autosave is true). Variable scope applies.


var thekids = db.filter(key.name).sort() /* prepares to fetch all documents with a name attribute (kids). also sorting ascending; the parameter 'desc' could be used to reverse the sort order. */
var sales =  db.filter(key.candysales).sort() /*prepares to search for all sales */
var kidsPurchases = db.filter(candysales.kidId = thekids.id) /* prepares the search for all candysales. the query 'thekids' did not have to be executed to be used in the 'kidspurchases' query. */
var kidsPurchasesSubset = db.filter(candysales.kidId = thekids.id).keys(thekids.name, candysales.candyname,candysales.date); /* prepares to fetch only certain properties from the data storage */


kidsPurchasesSubset.each = function (doc) {
    var element = createElement("div");

    element.textContent = "Name: "+doc.name+" - Candy: "+doc.candyname+" - Date: "+doc.date; 

document.getElementById("kidList").appendChild(element);   
} /*runs for each item getted/setted when a query is executed.*/


db.index("candyname",false); /* creates an index to accelerate searching by the candyname property. */




The second parameter in the index() function is for uniqueness. The false value indicates the index will allow for duplicate values, but will still create the index as a mean to accelerate partitioning and searching a specific subset of data.

All operations would be atomic by default, but the data handling functions get, set, filter, unset, del, add, save and abort would allow for a transaction='string' parameter.
this parameter would cause the changes to not be commited even if the autosave() setting is true. save(transaction='transid') would commit the transaction. abort(transaction='transid') would rollback all pending changes.

Vyatta (Openswan) versus SonicWALL IPSec

After some hair pulling, I found out how to set up a Vyatta <-> SonicWALL IPSec site-to-site tunnel.

Vyatta 4.1.4 uses Openswan 2.4.2, which can interoperate with SonicWALL. turns out it needs some specific configurations though, and some of those options were not available on the configuration setup.
Thankfully, being linux I could just vim/etc/ipsec.conf and tweak the Openswan settings.


#ipsec.conf
#these are the options which should be in your tunnel:
esp=3des-md5 # instead of esp=3des-md5-modp1024
pfs=yes # this is changeable by the configuration commands I believe
keyexchange=ike #didnt exist


Vyatta, network setup.

VICTORY! or almost...

I managed to set up the internet, NAT, firewall, poke the holes I needed. Now all that's there to it is the VPN tunnel.

I have also set up a separate machine running Dnsmasq as a DNS cache/DHCP server, and a HTTP(S) reverse proxy. Which is nice, because I have a single fixed IP and I want different machines to serve separate subdomains. like using Zimbra to serve webmail.mydomain.com, my database web interface from db.mydomain.com, and the plain websites from www.mydomain.com.

Word of advice: use the Apache Header module to make sure you are telling the browser to NOT cache your pages. at least while you are setting it up. Would have saved me a few headaches if I had done it prior to trying to set up the reverse proxy.

Rename VMWare Server 2.0 bridges

After my brilliant eth* renaming merry-go-round, I had another simple, but annoyingly hard-to-find-info about mission: change bridge names (just the labels) in VMWare.

After hastily running through the settings wizard on running vmware-config.pl, I realized that I had 4 bridges:
Bridge
Bridge(2)
Bridge(3)
Bridge(4)
Which are not very helpful names. Sure, I could have given the right names from the start, but I am just human. Everyone looks back and say ' I could hve done that one better' right?
After NOT finding any info on how to do it, I just tried the shotgun approach and got to dig on the vmware-config.pl command options.

Turns out I am a lucky guy. When i got to the network reconfiguration section, I told I wanted to change network settings, but instead of using the wizard ( as I always do - many people might do it too) I used the editor option. there we can change the network setting of all configured bridges, NATs and host-only networks. So I renamed all my bridges to "Bridge eth*" and it was all set.

Changing ethernet names in Ubuntu 8.04

So, I was trying my stuff around Vyatta, and realized I didn't have enough NICs to virtualize everything I had. Our company's basic routing landscape involves two modems, one device acting as router + DHCP + DNS cache+access point (802.11g), another AP (802.11n 5.0Ghz), and the VPN router (RIP). The server only had 2 gigabit NICs though. If I moved the DHCP, DNS cache and VPN routing to VMs, I'd have to plug the 2 modems and the 2 wireless routers on it. Tha put me on a deficit of 2 network cards.

Thankfully, I had some extra hardware lying around, I picked up 2 spare realtek-based NICs and plugged them on the server. 100Mbit/s is plenty for our current ADSL speeds.

That was when I hit a small problem: ifconfig -a only showed eth0, eth1 and eth2. One of the NICs was dead.

Oh, well. It would be a matter of switchingthe cards. It was not top-prority, so I went to grokking the software configuration. After some time I remembered I had some more cards lying on another machine, which was not in use. And those were some good 3com cards. I went on and picked out both of my realtek cards and plopped the 3com in.

Then I noticed something: ifconfig showed eth0 and eth1 (onboard gigabit), then eth3 and eth4.

Being a nitpicky guy I wanted eth2 back! Well, remembering which card held which eth* alias between boots is actually a good thing, but in this case i wanted to use eth2. Just for the fun of it.

Using the same eth* alias can also save reconfiguration trouble for people who had a NIC die on them. Replace the hardware, make the system poin to the new card with the old name, everything works again.

At least to me it was a bit hard to dig out the information on where it was, but I found it.

Ubuntu (udev) has this little file where it registers the eth* name given to each NIC to make naming persstent between boots. If you want to change some ethernet number, just go there and change it to your liking:

run via the command line:
sudo vim /etc/udev/rules.d/70-persistent-net.rules
(or use your editor of choice)

Vyatta inside VMWare

A bit about my job:

I am the IT jack-of-all-trades on my company's office. We have a few offices acattered around the blue marble, connected by VPN.

My VPN router died on monday, and we scrambled for a solution. Living in Brazil, a device which costs U$ 499 in the U.S. magically soars up to U$ 1,600.00 inside our country... Given the global economic downturn (and me liking open source), I looked for something baed on open source, but which is not a 'hobby job'. I had to feel confident about the product.

Also, having most of our infrastructure virtualized on VMWare Server 2.0, a virtualized router would come in handy, as long as virtualization does not impact performance.

I ended up finding Vyatta (http://www.vyatta.com), which looks like an interesting solution so far. I have been experimenting with it, and have managed the most basic stuff (NAT masquerading, set up pppoe, remote SSH admin). I will post more about it later (after I figure it out :) ).

jQuery UUID extension

This is a conversion of a few functions I make use of to a jQuery plugin.

This stems out from my need to be sure to have unique IDs, but I dont want to waste time reviewing code to make sure it is really unique, so as to not collide with other IDs on my page. I can have a random amount of forms at the same time in an inordinate amount of tabs, and each form has several form elements, and making sure they are all unique can be painstaking.

So I track my forms by registering the id of each form in an object array.

Naturally, other people may have other uses for the UUIDs.

UUIDs are generated in the format: 00000000-0000-0000-0000-000000000000 , with an optional prefix.

Usage 1: define the default prefix by using an object with the property prefix as a parameter which contains a string value;

jQuery.uuid({prefix: 'id'}); //returns nothing, sets default prefix to string 'id'

Usage 2: call the function jQuery.uuid() with a string parameter p to be used as a prefix to generate a random uuid;

jQuery.uuid('foo-'); // returns 'foo-<uuid>'

Usage 3: call the function jQuery.uuid() with no parameters to generate a uuid with the default prefix; The default prefix is '' (empty string)

jQuery.uuid(); //returns '<prefix><uuid>'

Get it at http://plugins.jquery.com/project/uuid

Ressurectum

After having registered this blog with my nick of choice on the 'web, I figured I could actually use it for anything other than gather bit-dust.

Such as posting some of my codes...

Brazilian Joe's Hideout

Ping? Pong!

Bling!

It's Alive!!!