Get list or array of field values depending on another field

[Nelson_Marquez]

Hi

I have a table which for each occurrence of a field can have one, two or even three occurrences of another field, as shown below:

I mean, I'd like to get a column for 'Local de descarga' and to introduce the 'Local de carga' items, hereabove identified as "Source 1" and "Source 2" in columns, ie, 'Local de carga 1' and 'Local de carga 2'. The rows would be, in this example, respectively "ES15-Castellon-CLH Depot"; "Huelva Refinery" and "Castellon Refinery".

I depict below what I mean, here created with the help of Excel:

This sounds like handling arrays, but since I'm relatively new with PowerBI I have no idea on how to do this.

Help would be much appreciated.

Nelson

[DataInsights · Accepted solution]

@Nelson_Marquez,

Try this in Power Query. Copy the code starting with GroupRows and paste into your query editor.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ihNzSlL1A1KTcvMSy2qVNJRcg02NNV1TiwuSc3Jyc/TdfbxUEhJLcgvUYrViVZCiBOrA6bOEKLQTLcktRhVxginjDGqTCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Local de carga" = _t, #"Local de descarga" = _t]),
    GroupRows = Table.Group(
      Source, 
      {"Local de descarga"}, 
      {"Local de carga group", each Table.SelectColumns(_, "Local de carga")[Local de carga]}
    ),
  ExtractValues = Table.TransformColumns(
      GroupRows, 
      {"Local de carga group", each Text.Combine(List.Transform(_, Text.From), "|"), type text}
    ),
  SplitLocalDeCarga = Table.SplitColumn(
      ExtractValues, 
      "Local de carga group", 
      Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), 
      {"Local de carga group.1", "Local de carga group.2", "Local de carga group.3"}
    ),
  RenameColumns = Table.RenameColumns(
      SplitLocalDeCarga, 
      {
        {"Local de carga group.1", "Local de carga 1"}, 
        {"Local de carga group.2", "Local de carga 2"}, 
        {"Local de carga group.3", "Local de carga 3"}
      }
    )
in
  RenameColumns

Result:

Sample data: