Find and Repeat Value

[Carlo1975]

Hi, I have a table with 3 columns:

IdBuilding	SingleType	Price
1	Residential	120
1	Commercial	150
1	Parking	50
1	Office	55
2	Hospital	250
2	Parking	15
3	Hotel	1200
3	Parking	35
3	Office	25
3	Commercial	25

I have to add a new column. For each IDBuilding I have to insert in the new column the first value find in SingleType, like this:

IdBuilding	SingleType	Price	Building Type
1	Residential	120	Residential
1	Commercial	150	Residential
1	Parking	50	Residential
1	Office	55	Residential
2	Hospital	250	Hospital
2	Parking	15	Hospital
3	Hotel	1200	Hotel
3	Parking	35	Hotel
3	Office	25	Hotel
3	Commercial	25	Hotel

and so for IdBuilding 1 I will write Residential on all the 4 row of ID 1, IdBuilding 2 I will write Hospital and 3 Hotel

[mahoneypat · Accepted solution]

This could be done in a DAX column if you first add an index, but here is one way to do it in the Query Editor.  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kxxKs3MScnMS1fSUQoGUjmpIZUFqUBOQFFmcqpSrE60kiGQF5RanJmSmleSmZgD5BkaGcBlnPNzc1OLkqESpgiJgMSibIi5SIL+aWkgY4FipmAxIyDTI7+4ILMErN8IqtQIRb8hRK0xWG1JKtQFBnBBhEpjhEq4TUYIMRS3gsRjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"IdBuilding", Int64.Type}, {"SingleType", type text}, {"Price", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each let thisid = [IdBuilding] in List.First(Table.SelectRows(#"Changed Type1", each [IdBuilding] = thisid)[SingleType]), type text)

in
    #"Added Custom"

Regards,

Pat

[Carlo1975]

No, I have wronged...I have problem also with power query if I refresh all the table (not only the first 1000 rows). I have totally 12.000 rows.

[Carlo1975]

Hi Pat, your solution it's ok, but I have a problem, now the Power BI fails to load file. it is very slow, after 10 hours it still fails to update the data. Power Query functions, Power BI no. Have you any idea?