How can I get the first value in a collumn

[leandross89]

Hi Everybody,

I would like to create the collumn 1 and Collumn 2, as you can see the collumn 1 has a location and for each location I´d like to generate 1 number but repeating it for the same device. The collumn 2 I´d like to repeat the topo information of the device 1  for the others devices on the same location. 

LOCATION	DEVICE	TOPO	Collumn 1	Collumn 2
140059	1	2G	1	2G
140059	2	4G	1	2G
140059	4	4G	1	2G
140534	1	4G	2	4G
140534	3	2G	2	4G
140534	4	2G	2	4G
140701	1	2G	3	2G
140701	2	4G	3	2G

Could you help me?

[mahoneypat · Accepted solution]

Here's one way to do it in the query editor.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQxMDC1VNJRMgRiI3elWB0kMSMgNkETM0ERMzU2gepFFzNGMQ8iZoIiZm5giGEvRAxmbywA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [LOCATION = _t, DEVICE = _t, TOPO = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"LOCATION", Int64.Type}, {"DEVICE", Int64.Type}, {"TOPO", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"LOCATION"}, {{"All", each _, type table [LOCATION=nullable number, DEVICE=nullable number, TOPO=nullable text, Collumn 1=nullable number, Collumn 2=nullable text]}}),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each let ind = [Index] in Table.AddColumn([All], "Index", each ind)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each let firsttopo = [All]{0}[TOPO] in Table.AddColumn([Custom], "FirstTOPO", each firsttopo)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"Custom.1"}),
    #"Expanded Custom.1" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom.1", {"LOCATION", "DEVICE", "TOPO", "Collumn 1", "Collumn 2", "Index", "FirstTOPO"}, {"LOCATION", "DEVICE", "TOPO", "Collumn 1", "Collumn 2", "Index", "FirstTOPO"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom.1",{{"LOCATION", Int64.Type}, {"DEVICE", Int64.Type}, {"TOPO", type text}, {"Collumn 1", Int64.Type}, {"Collumn 2", type text}, {"Index", Int64.Type}, {"FirstTOPO", type text}})
in
    #"Changed Type1"

Pat

[leandross89]

is there any example to do in DAX?