Unable to Extract values from [List]

Hi Chandra_maha ,

 

This is my test table 1:

 

Table 2:

 

Duplicate ID column in table 1:

 

Split column as following steps:

 

You will get a table like this:

 

Then add an index column (this is important):

 

Close and apply to Power BI. Creata two new columns in table 1:

 

ID_new = IF('Table 1'[ID - Copy] = MINX(FILTER('Table 1','Table 1'[ID] = EARLIER('Table 1'[ID])),'Table 1'[ID - Copy]),'Table 1'[ID]) 

Name_new = IF('Table 1'[ID - Copy] = MINX(FILTER('Table 1','Table 1'[ID] = EARLIER('Table 1'[ID])),'Table 1'[ID - Copy]),'Table 1'[Name])

 

 

Relationship between table 1 and table 2.

 

Create a table visual like below shown and sort by index column:

If you don't want to show index column, please set it in specific column as belown shown (You'd better rename index column, in the example it is a, so you can reduce column width)

 

please refer the pbix file.

 

M code in power query for your reference:

 

 #"Promoted Headers" = Table.PromoteHeaders(Sheet43_Sheet, [PromoteAllScalars=true]),
    #"Duplicated Column" = Table.DuplicateColumn(#"Promoted Headers", "ID", "ID - Copy"),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Duplicated Column", {{"ID - Copy", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "ID - Copy"),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"ID - Copy", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type)
in
    #"Added Index"

 

 

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.