cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
brunofs123 Frequent Visitor
Frequent Visitor

dataset is a mess - help with power query

Hello!!!   I've just recently posted, hope there is no problem asking for help again  in such a tight time.

 

 

I have a dataset that is a complete MESS.  the PBIX file is uploaded here:  https://1drv.ms/u/s!Attmx4FL6atgimhPZhv9E28Z8y8f

 

Is something like this :

 

ID

ADRESS

Column6

Column7

Column8

Column9

Column10

null

null

6JAN2017

13JAN2017

20JAN2017

27JAN2017

3FEV2017

PERE 02601

Praia do Pereque - Cubatão

P

P

P

P

P

null

null

6JAN2017

13JAN2017

20JAN2017

27JAN2017

3FEV2017

GRDE 02271

No píer da praia municipal de Miguelópolis

P

P

P

P

P

GRDE 02273

Na parte central da praia Municipal de Miguelopolis.

P

P

P

P

P

null

null

6JAN2017

13JAN2017

20JAN2017

27JAN2017

3FEV2017

MOGU 02351

Cachoeira das Emas

I

I

I

I

I

QUEM 02700

Praia em frente à R. Ver. Carlos Ravanini 336

I

P

P

P

I

null

null

8JAN2017

13JAN2017

20JAN2017

27JAN2017

3FEV2017

SOIT 02801

Clube ACM de Sorocaba

***

null

null

P

null

SOIT 02601

Prainha do Piratuba

***

null

null

P

null

null

null

8JAN2017

13JAN2017

20JAN2017

27JAN2017

3FEV2017

 

 

Actually it goes faaar beyond column 10, something like 60 more columns.

 

What is supposed to show:

 

 

ID

ADRESS

date

value

PERE 02601

Praia do Pereque - Cubatão

6JAN2017

P

PERE 02602

Praia do Pereque - Cubatão

13JAN2017

P

PERE 02603

Praia do Pereque - Cubatão

20JAN2017

P

PERE 02604

Praia do Pereque - Cubatão

27JAN2017

P

PERE 02605

Praia do Pereque - Cubatão

3FEV2017

P

GRDE 02271

No píer da praia municipal de Miguelópolis

6JAN2017

P

GRDE 02271

No píer da praia municipal de Miguelópolis

13JAN2017

P

GRDE 02272

No píer da praia municipal de Miguelópolis

20JAN2017

P

GRDE 02273

No píer da praia municipal de Miguelópolis

27JAN2017

P

GRDE 02274

No píer da praia municipal de Miguelópolis

3FEV2017

P

(...)

(...)

(...)

(...)

 

 

Problems:

- I should not fill up or down the "Columns" with dates/values, because it will fill with worng statements; The same goes for "ID" and "Adress". I've done it before.

- other than "P", and "I", there are a lot of possible values such as "*" ; "***"; "-"; "Sb'";  that must put into consideration

 

I've been trying unpivot columns but with no success...

 

 

Thanks in advance!!!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: dataset is a mess - help with power query

So you cannot invest some time to prepare sample data for the data we've been working on so far?

 

Anyway - this is a solution that will allocate the leading dates to all the rows below who have an entry in "Reservatório/Rio" .. until the next row of dates appear:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("vVc9k9s2EP0rO1c5HlunD/vilBDJ0+FG/DAoKcl5XOAk+owMRcoQeeNJlUwKV67cpYonhSeeceUuZfjHsqAkkqJIiTrHmYMk4EC+BXbfvgWePTs5eZC37y6J1W13HmO3c5b3u72832vn/bNzY7J5vLfpP3+QYjJx7QmZ/BnCQPJg5q0MJL/APAwiHkQezEK45lL9GLN5HMxCnHfWH7r1ySBDcITk05jP+Qqvz/3AS95LNROrmTmHh8A8EYSQ/HoT8yVoPpd8WYlbwL7k+LAUp5d8yqWXfFrBO5ILrlaohbi+efIpQENWeBvCRSjFz7gTta/79+9nHsz7TYA5uJ6UInjJ4Z6TfPKk+peJjwdcmXwheJB84N/gC24/M5F11xZIJK65wiiteRyJ4Ibf7d0hv+WL5K/lUW+zcNYCPZyD481kCBTuST5fcFjI5PPrFros8nyYcEWH8p4sbRt5gD7jC4z1agfYjDm8kJ7iDQ9hsfJVCD/y6csIQxxfg8NjXywjXhnlQqR3oa0MkIOxnIY+usDD3iKUkbcEK3kfR2IaLuF7EcxSY14BeJuytUaUa1Wc+1xIdI7ilBTLhZhnpEl9UPraA7imSXECqZ9F0A39wuKcrcU2wpyK4z253pwp+FxkATklCMajO8BtfIbbueRyJuaY2QvpLdVGLwRyjN8KVJjjgZmH60K9UHSi+CV9foPxfZjuPP6p8PBeB/aF72OOLVegKTEUczRkfRphF/miMmIJphdxP/lb3qQ8winS10roThP0jUdG/KVIPVriywGQzevOqxaYcSCmYsH9VIGXEQpamTEN0bwsNZM/wBgZFcmxFwXVQaBuY15fhaGffFZOQq8hgeSr2MsXemh1QwwiGPHUFzNMWzOUHto5xUKEmw0RaX5SELmtRbMWTDzZwshJH+PDUP0CEQjo9c5qjKK311ZpFC+QX9W0VeUqiq8PqDDSsYXCICXKQxiIqIyDJL1BA8mHWGGOhBclH7cRKdlGzFr70Wm7c4qFuYeDTqc4eFwYdLuFAfba3WzwJBs0K8Obmdo6vDdX+4wkb2zQbWDUBocMTToiwx1/MG/mYWlUx4t1Ea3T/YwdzHANNiGj5J1CviSDMWH09JJohBnJ27UFRigBywbNtnTbTN5a+KhlT2y4sBm9sq2U3OV2jIHkrcFAJ2DinEWUnXNKrOQNaYq79gwZ0T6h1gUpcrpY9P8LMHUKeLg+BnwBHrN1e4J+1W0LHENnNlBaj1hRBlgKptARlrHUjsao61DzkCZUF5WKCkmbvX23ytEAuL6e5SusfZkwZNmoOrEKL0KfDofUGrjbfj1nBhIb8Gyei3ftIbyEoQ3HfdgAjcgFHe3KfmnvNavQVbqzp2MDzLFFNeqkab/30L6DlLz7gZrIPxuu7JQlztNqyKaLU9szQDdAI6Zjg0stHYuJgZKU/M4GVLPdJpU8a+2yzDYZ9Ipq3CmrcdYeXfLgOJ3vnHu36+6TTXdTSsnABmOsDaluuGDazECv5PEiuUOVc9wRwdrJxldK2VDSNIMmb5Lf7JPN0Xbfd6WQ0NEYyYB/1qAgSrtFtUZDattOQM7W/ugqf/SKg28Lg25xJgtI+lg7G+xefPN7LzoMiwdRaZaqGFM/ho5e1O1DWVtRb5lBsUjpxMXiimXGBW2IvnIrUA4Ix57rQgPNOnhdOCTFeerXK1hzjAuKtYVM6Ip52x44iHOcfpfO5lUqoaM2oFK01gKxS/5GoLm0NlDWbQBL0WZ1Tcb7F17E53jcTe8ldBm10itucPPPx2OStHRyX2krNemAKWq7XwBVKAJPW43Vuhqkqp7tfPp1GFhDMGaAEIygnGEzNHV8+aLd2YiZlaTm+zv5ijp1pMRjgEmNxv9PW8hqXy61d7s3Va3363r8+b8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [UGRHI = _t, #"Reservatório/Rio" = _t, Código = _t, #"Local de Amostragem" = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"UGRHI", type text}, {"Reservatório/Rio", type text}, {"Código", type text}, {"Local de Amostragem", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "IsDate", each [#"Reservatório/Rio"] = "" or [#"Reservatório/Rio"] = null),
    DateIndex = Table.AddColumn(#"Added Custom", "DateIndex", each if [IsDate] then [Index] else null),
    #"Filled Down" = Table.FillDown(DateIndex,{"DateIndex"}),
    MagicUnpivot = Table.UnpivotOtherColumns(#"Filled Down", {"UGRHI", "Reservatório/Rio", "Código", "Local de Amostragem", "Index", "IsDate", "DateIndex"}, "Attribute", "Value"),
    #"Merged Queries" = Table.NestedJoin(MagicUnpivot,{"DateIndex", "Attribute"},MagicUnpivot,{"Index", "Attribute"},"MagicUnpivot",JoinKind.LeftOuter),
    #"Expanded MagicUnpivot" = Table.ExpandTableColumn(#"Merged Queries", "MagicUnpivot", {"Value"}, {"Date"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded MagicUnpivot", each ([IsDate] = false)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index", "IsDate", "DateIndex", "Attribute"})
in
    #"Removed Columns"

If you struggle to integrate that code into your file, you find a link in my signature below that might help you with that.

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




9 REPLIES 9
Super User
Super User

Re: dataset is a mess - help with power query

Your sample result is missing the critical data:

How about that item: Does it have data and if yes, which data belongs to the  next one ("Cachoeira das Emas")?

GRDE 02273

Na parte central da praia Municipal de Miguelopolis.

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




brunofs123 Frequent Visitor
Frequent Visitor

Re: dataset is a mess - help with power query

Capturar.PNG

 

Hello!

 

I'm afraid this is part of the mess i was referring to... haha

 

Se the above picture. Red "belongs" to red line , and the same goes for blue

 

for instance, at the "MOGU 02351"  (it's the ID of the point), located at "Cachoeira das emas", on the specific day of  06-jan-2017, there was ONE water monitorig, which resulted in the value "I". (just for clarification, this means "unfit for bathing". those are river/beaches points of monitoring.

 

the same goes for the ID QUEM 02700..

 

 

thank you.

Super User
Super User

Re: dataset is a mess - help with power query

And how would you like to see these examples represented in your results-table?

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




brunofs123 Frequent Visitor
Frequent Visitor

Re: dataset is a mess - help with power query

Hello,

 

The point is summarize, for each ID, the values correspondets for each dates, like i've post originally.

 

The result will be something like pic below, but with + thousands of rows.

 

Capturar2.PNG

Super User
Super User

Re: dataset is a mess - help with power query

So you cannot invest some time to prepare sample data for the data we've been working on so far?

 

Anyway - this is a solution that will allocate the leading dates to all the rows below who have an entry in "Reservatório/Rio" .. until the next row of dates appear:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("vVc9k9s2EP0rO1c5HlunD/vilBDJ0+FG/DAoKcl5XOAk+owMRcoQeeNJlUwKV67cpYonhSeeceUuZfjHsqAkkqJIiTrHmYMk4EC+BXbfvgWePTs5eZC37y6J1W13HmO3c5b3u72832vn/bNzY7J5vLfpP3+QYjJx7QmZ/BnCQPJg5q0MJL/APAwiHkQezEK45lL9GLN5HMxCnHfWH7r1ySBDcITk05jP+Qqvz/3AS95LNROrmTmHh8A8EYSQ/HoT8yVoPpd8WYlbwL7k+LAUp5d8yqWXfFrBO5ILrlaohbi+efIpQENWeBvCRSjFz7gTta/79+9nHsz7TYA5uJ6UInjJ4Z6TfPKk+peJjwdcmXwheJB84N/gC24/M5F11xZIJK65wiiteRyJ4Ibf7d0hv+WL5K/lUW+zcNYCPZyD481kCBTuST5fcFjI5PPrFros8nyYcEWH8p4sbRt5gD7jC4z1agfYjDm8kJ7iDQ9hsfJVCD/y6csIQxxfg8NjXywjXhnlQqR3oa0MkIOxnIY+usDD3iKUkbcEK3kfR2IaLuF7EcxSY14BeJuytUaUa1Wc+1xIdI7ilBTLhZhnpEl9UPraA7imSXECqZ9F0A39wuKcrcU2wpyK4z253pwp+FxkATklCMajO8BtfIbbueRyJuaY2QvpLdVGLwRyjN8KVJjjgZmH60K9UHSi+CV9foPxfZjuPP6p8PBeB/aF72OOLVegKTEUczRkfRphF/miMmIJphdxP/lb3qQ8winS10roThP0jUdG/KVIPVriywGQzevOqxaYcSCmYsH9VIGXEQpamTEN0bwsNZM/wBgZFcmxFwXVQaBuY15fhaGffFZOQq8hgeSr2MsXemh1QwwiGPHUFzNMWzOUHto5xUKEmw0RaX5SELmtRbMWTDzZwshJH+PDUP0CEQjo9c5qjKK311ZpFC+QX9W0VeUqiq8PqDDSsYXCICXKQxiIqIyDJL1BA8mHWGGOhBclH7cRKdlGzFr70Wm7c4qFuYeDTqc4eFwYdLuFAfba3WzwJBs0K8Obmdo6vDdX+4wkb2zQbWDUBocMTToiwx1/MG/mYWlUx4t1Ea3T/YwdzHANNiGj5J1CviSDMWH09JJohBnJ27UFRigBywbNtnTbTN5a+KhlT2y4sBm9sq2U3OV2jIHkrcFAJ2DinEWUnXNKrOQNaYq79gwZ0T6h1gUpcrpY9P8LMHUKeLg+BnwBHrN1e4J+1W0LHENnNlBaj1hRBlgKptARlrHUjsao61DzkCZUF5WKCkmbvX23ytEAuL6e5SusfZkwZNmoOrEKL0KfDofUGrjbfj1nBhIb8Gyei3ftIbyEoQ3HfdgAjcgFHe3KfmnvNavQVbqzp2MDzLFFNeqkab/30L6DlLz7gZrIPxuu7JQlztNqyKaLU9szQDdAI6Zjg0stHYuJgZKU/M4GVLPdJpU8a+2yzDYZ9Ipq3CmrcdYeXfLgOJ3vnHu36+6TTXdTSsnABmOsDaluuGDazECv5PEiuUOVc9wRwdrJxldK2VDSNIMmb5Lf7JPN0Xbfd6WQ0NEYyYB/1qAgSrtFtUZDattOQM7W/ugqf/SKg28Lg25xJgtI+lg7G+xefPN7LzoMiwdRaZaqGFM/ho5e1O1DWVtRb5lBsUjpxMXiimXGBW2IvnIrUA4Ix57rQgPNOnhdOCTFeerXK1hzjAuKtYVM6Ip52x44iHOcfpfO5lUqoaM2oFK01gKxS/5GoLm0NlDWbQBL0WZ1Tcb7F17E53jcTe8ldBm10itucPPPx2OStHRyX2krNemAKWq7XwBVKAJPW43Vuhqkqp7tfPp1GFhDMGaAEIygnGEzNHV8+aLd2YiZlaTm+zv5ijp1pMRjgEmNxv9PW8hqXy61d7s3Va3363r8+b8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [UGRHI = _t, #"Reservatório/Rio" = _t, Código = _t, #"Local de Amostragem" = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"UGRHI", type text}, {"Reservatório/Rio", type text}, {"Código", type text}, {"Local de Amostragem", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "IsDate", each [#"Reservatório/Rio"] = "" or [#"Reservatório/Rio"] = null),
    DateIndex = Table.AddColumn(#"Added Custom", "DateIndex", each if [IsDate] then [Index] else null),
    #"Filled Down" = Table.FillDown(DateIndex,{"DateIndex"}),
    MagicUnpivot = Table.UnpivotOtherColumns(#"Filled Down", {"UGRHI", "Reservatório/Rio", "Código", "Local de Amostragem", "Index", "IsDate", "DateIndex"}, "Attribute", "Value"),
    #"Merged Queries" = Table.NestedJoin(MagicUnpivot,{"DateIndex", "Attribute"},MagicUnpivot,{"Index", "Attribute"},"MagicUnpivot",JoinKind.LeftOuter),
    #"Expanded MagicUnpivot" = Table.ExpandTableColumn(#"Merged Queries", "MagicUnpivot", {"Value"}, {"Date"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded MagicUnpivot", each ([IsDate] = false)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index", "IsDate", "DateIndex", "Attribute"})
in
    #"Removed Columns"

If you struggle to integrate that code into your file, you find a link in my signature below that might help you with that.

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




brunofs123 Frequent Visitor
Frequent Visitor

Re: dataset is a mess - help with power query

Hello

Regarding to So you cannot invest some time to prepare sample data for the data we've been working on so far?

 

I don't understand, i've been strugling with this dataset for almost a week, posting here is always my last resort. I've post my dataset (pbix. file) and did everything i could to be clear enough, despite my lack of english.

 

But i appreciate your efforts, going to try your suggetion!

 

regards

Super User
Super User

Re: dataset is a mess - help with power query

Hi @brunofs123

apologies for assuming you didn't bother to post the needed data.

 

I was hoping to see how QUEM 02700 or SOIT 02601 would look in your result-table, as they are included in your red and blue - example. But instead you've posted data from items that are not included in your sample data at all. That doesn't allow me to derive the logic of the transformation needed.

 

Please check out this post that gives tips how to post questions in a forum : https://social.technet.microsoft.com/wiki/contents/articles/28212.how-to-ask-a-power-bipower-querypo...

 

image.png

 

So when making future posts (you can make as many as you like in this forum Smiley Happy ) , please make sure to include samples of your "Desired Results" that match the sample data (current result or source data) you're posting.  

 

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




brunofs123 Frequent Visitor
Frequent Visitor

Re: dataset is a mess - help with power query

Hey there, you are absoluteley correct, my bad.

 

Providing the correct pictures according with the data set :

 

CURRENT RESULTS/DATASET (there are plenty more columns with dates, i only have picked 2):

 

 

UGRHIReservatório/RioCódigoLocal de AmostragemColumn6Column7
nullnullnullnull6JAN201713JAN2017
8Rio GrandeGRDE 02271No píer da praia municipal de MiguelópolisPP
nullRio GrandeGRDE 02273Na parte central da praia Municipal de Miguelopolis.PP
nullnullnullnull6JAN201713JAN2017
9Mogi-GuaçuMOGU 02351Cachoeira das EmasII
nullLago Euclides Morelli/Rib. MoquemQUEM 02700Praia em frente à R. Ver. Carlos Ravanini 336IP
nullnullnullnull8JAN201713JAN2017
10ItupararangaSOIT 02801Clube ACM de Sorocabanullnull
nullItupararangaSOIT 02601Prainha do Piratubanullnull

 

 

 

DESIRED RESULTS:

 

UGRHIReservatório/RioCódigoLocal de AmostragemDATEvalue
8Rio GrandeGRDE 02271No píer da praia municipal de Miguelópolis6JAN2017P
8Rio GrandeGRDE 02271No píer da praia municipal de Miguelópolis13JAN2017P
8Rio GrandeGRDE 02273Na parte central da praia Municipal de Miguelopolis.6JAN2017P
8Rio GrandeGRDE 02273Na parte central da praia Municipal de Miguelopolis.13JAN2017P
9Mogi-GuaçuMOGU 02351Cachoeira das Emas06/jan/17I
9Mogi-GuaçuMOGU 02351Cachoeira das Emas13JAN2017I
9Lago Euclides Morelli/Rib. MoquemQUEM 02700Praia em frente à R. Ver. Carlos Ravanini 33606/jan/17I
9Lago Euclides Morelli/Rib. MoquemQUEM 02700Praia em frente à R. Ver. Carlos Ravanini 33613JAN2017P
10ItupararangaSOIT 02801Clube ACM de Sorocaba8JAN2017null
10ItupararangaSOIT 02801Clube ACM de Sorocaba13JAN2017null
10ItupararangaSOIT 02801Prainha do Piratuba8JAN2017null
10ItupararangaSOIT 02601Prainha do Piratuba13JAN2017null

 

 

 

 

regards

Highlighted
Super User
Super User

Re: dataset is a mess - help with power query

Yes, that's what my code does.

Please check out this file also: https://1drv.ms/u/s!Av_aAl3fXRbehcNyeyLqHzAoUxomSw

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries