cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
brunofs123
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

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

9 REPLIES 9
ImkeF
Super User II
Super User II

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

brunofs123
Frequent Visitor

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.

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

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

brunofs123
Frequent Visitor

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

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

brunofs123
Frequent Visitor

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

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 🙂 ) , please make sure to include samples of your "Desired Results" that match the sample data (current result or source data) you're posting.  

 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

brunofs123
Frequent Visitor

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

Yes, that's what my code does.

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

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.