Forum Discussion
Find and Repeat Value
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
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
3 Replies
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
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?
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.
