dataset is a mess - help with power query

[brunofs123]

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!!!

[ImkeF]

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.

[brunofs123]

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.

[ImkeF]

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

[brunofs123]

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.

[ImkeF · 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.

[brunofs123]

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

[ImkeF]

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...

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.  

[brunofs123]

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):

UGRHI	Reservatório/Rio	Código	Local de Amostragem	Column6	Column7
null	null	null	null	6JAN2017	13JAN2017
8	Rio Grande	GRDE 02271	No píer da praia municipal de Miguelópolis	P	P
null	Rio Grande	GRDE 02273	Na parte central da praia Municipal de Miguelopolis.	P	P
null	null	null	null	6JAN2017	13JAN2017
9	Mogi-Guaçu	MOGU 02351	Cachoeira das Emas	I	I
null	Lago Euclides Morelli/Rib. Moquem	QUEM 02700	Praia em frente à R. Ver. Carlos Ravanini 336	I	P
null	null	null	null	8JAN2017	13JAN2017
10	Itupararanga	SOIT 02801	Clube ACM de Sorocaba	null	null
null	Itupararanga	SOIT 02601	Prainha do Piratuba	null	null

DESIRED RESULTS:

UGRHI	Reservatório/Rio	Código	Local de Amostragem	DATE	value
8	Rio Grande	GRDE 02271	No píer da praia municipal de Miguelópolis	6JAN2017	P
8	Rio Grande	GRDE 02271	No píer da praia municipal de Miguelópolis	13JAN2017	P
8	Rio Grande	GRDE 02273	Na parte central da praia Municipal de Miguelopolis.	6JAN2017	P
8	Rio Grande	GRDE 02273	Na parte central da praia Municipal de Miguelopolis.	13JAN2017	P
9	Mogi-Guaçu	MOGU 02351	Cachoeira das Emas	06/jan/17	I
9	Mogi-Guaçu	MOGU 02351	Cachoeira das Emas	13JAN2017	I
9	Lago Euclides Morelli/Rib. Moquem	QUEM 02700	Praia em frente à R. Ver. Carlos Ravanini 336	06/jan/17	I
9	Lago Euclides Morelli/Rib. Moquem	QUEM 02700	Praia em frente à R. Ver. Carlos Ravanini 336	13JAN2017	P
10	Itupararanga	SOIT 02801	Clube ACM de Sorocaba	8JAN2017	null
10	Itupararanga	SOIT 02801	Clube ACM de Sorocaba	13JAN2017	null
10	Itupararanga	SOIT 02801	Prainha do Piratuba	8JAN2017	null
10	Itupararanga	SOIT 02601	Prainha do Piratuba	13JAN2017	null

regards

[ImkeF]

Yes, that's what my code does.

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