Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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.
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
51 | |
19 | |
12 | |
11 |