cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Nelson_Marquez
Regular Visitor

Get list or array of field values depending on another field

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:

Nelson_Marquez_0-1604420951210.png

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:

Nelson_Marquez_1-1604421480119.png

 

 

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

1 ACCEPTED SOLUTION
DataInsights
Super User II
Super User II

@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:

DataInsights_0-1604432461073.png

 

Sample data:

DataInsights_1-1604432480095.png

 

View solution in original post

1 REPLY 1
DataInsights
Super User II
Super User II

@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:

DataInsights_0-1604432461073.png

 

Sample data:

DataInsights_1-1604432480095.png

 

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.