Трансформация на данни с Using Locale в Power Query

Using Locale в Power Query е много полезен за преобразуване на данни в един все по-често срещан случай. В съвременния глобализиран свят все повече хора работят с файлове, в които дати, числа, мерни единици са по друг стандарт от техния. Например на долната картинка имаме колона с двайсетина дати.

Using Locale in Power Query

Още от пръв поглед, всеки опитен анализатор, ще надуши, че нещо не е наред. Всички дати започват с 10. Да кажеш случва се. Обаче са подравнени наляво. А в Excel датите са всъщност числа, което означава, че трябва да са подравнени вдясно.

 

Using Locale in Power Query

Ако се вгледате внимателно ще видите, че на редове 14-19 месеците са по-големи от 12. Но как е възможно това? Отговорът е много прост – данните идват от система, която ползва американския стандарт за дата ММ/ДД/ГГГГ. Тоест – първо са цифрите за месеца, после за деня и накрая за годината. Шантава работа, но така са свикнали там.

А за капак на всичко, някои от датите са с разделител точка (.) – отбелязаните със сини стрелки. Тях Excel ги е разпознал като дата. Неясно е обаче защо едни ги разпознава, а други – не.

Ако искаме да оправим тази каша с Excel формули, то ще ни се наложи да пишем доста дълга и сложна формула, която включва IF, VALUE, LEFT, RIGHT, ISNUMBER, DAY, YEAR, CONCATENATE. Сами се досещате за какъв ужас става въпрос.

Но можем да си спестим всичко това с помощта на Using  Locale в Power Query.

Обръщане на дати в български стандарт с Using Locale в Power Query

Първата стъпка е да заредим данните в Power Query (ако не знаете какво Power Query и как се ползва – вижте тук).

Using Locale in Power Query

Обърнете внимание на бутона, ограден в червено. Той показва какъв е форматът на данните. В случая е смесен – текст и числа (датите в Excel и Power Query са числа). Ние искаме да го обърнем в дати и затова трябва да кликнем на този бутон. Повече за смяната на формат в Power Query – тук.

След като натиснем бутона, появява се меню, в което отиваме най-долу и натискаме Using Locale.

Using Locale in Power Query

Тук Power Query ни показва в какъв форма възприема в момента стойностите (text) и според кой локален стандарт (English (United Kingdom)). А ние искаме да ги възприема като дати в американски стандарт – съответно трябва да изберем тези стойности:

След това даваме ОК и – всичко е готово. Или пък … не съвсем – вижте отбелязаните редове. Там стойностите са десети март, десети април, десети май.

Ако си спомняте, в тези клетки Excel беше разчел датите правилно и сега като ги обърнахме в американски стандарт, получаваме нещо, което не е вярно. Така научихме

Важен урок при работа с Power Query

Когато форматът на една колона е смесен (АВС|123) има риск с Using Locale да трансформираме данните по различен начин. Затова трябва да преглеждаме резултата от всяка трансформация, защото той може да не е това, което очакваме. В случая данните ни не са еднотипни. Едни са текст, други – валидна дата. Следователно преди да прибегнем до Using Locale, трябва да ги приведем в един и същи вид.

За целта първо променяме формата да стане текст:

Сега вече едните дати са дати, а други – дата и час.

Най-лесно е да премахнем 00:00:00 с Replace Values:

Като кажем да замени 00:00:00 с нищо

Ето го резултатът

И след това вече можем да преминем към промяна на формата с Using Locale, както беше описано по-горе. Крайният резултат е точно каквото очакваме:

Остава само с Home > Close and load to да заредим данните в Excel. Може да видите, че всичко е точно:

Обобщение

Смяната на формата на данните с Using Locale в Power Query е много лесен и удобен начин да обърнем дати (и други числа), които не са форматирани по български стандарт. Трябва обаче да се внимава в случаите, когато Excel не възприема всички данни еднакво. За най-сигурно – ако видим, че форматът е смесен (бутон ABC | 123) да изследваме данните, за да разберем къде са разликите. След това ги обръщаме в текстови формат и ги уеднаквяваме с Replace, Split, Trim и други трансформации, които предлага Power Query. Едва след тази стъпка сме готови да използваме Locale

Публикувано в Хитрини. Постоянна връзка.