Duplicate line based on a list contain in one column

[LY18]

Hi 

today i got a new issue 😉

i got a source table with information that are presented like this

ID MEASURE	ID_PARAMETER	PARAMETER_NAME	EQT_CONCERNED	Group_Value	Group_SPEC
1	1	PARAM1	EQT1,EQT2,EQT3	50.45	100
2	1	PARAM1	EQT1,EQT2,EQT3	50.69	100
3	1	PARAM1	EQT1,EQT3	60.36	100
4	2	PARAM2	EQT1,EQT4	7.3	50
5	2	PARAM2	EQT1,EQT5	10.84	50
6	2	PARAM2	EQT5,EQT6,EQT9	10.4	50
7	2	PARAM2	EQT5,EQT6,EQT9	5.6	60
8	3	PARAM3	EQT1,EQT10,EQT9	7.5	10
9	3	PARAM3	EQT2,EQT5	4.6	10
10	3	PARAM3	EQT2,EQT3	2.3	10
11	3	PARAM3	EQT4,EQT10	2.5	50
12	3	PARAM3	EQT6,EQT7	54.6	50

In order to merge information by EQT and do some more calculation i would need to split the list of "EQT_CONCERNED" to get 1 line for each EQT (not split in column, but in rows ...) ... so for exemple line 1 should be transformed into 3 lines

1

1

PARAM1

EQT1

50.45

100

1

1

PARAM1

EQT2

50.45

100

1

1

PARAM1

EQT3

50.45

100

do you have some advise to do it in power bi query ?

thanks in advance for your help

regards

[v-jayw-msft]

Hi @LY18 ,

Have your problem be solved? Please consider accept the answer as a solution if it worked.

Best Regards,

Jay

Community Support Team _ Jay Wang

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

[v-jayw-msft · Accepted solution]

Hi @LY18 ,

Select the column and under Split Column -> By Delimiter -> Advanced options, you will see Split into rows option.

Best Regards,

Jay

Community Support Team _ Jay Wang

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

[amitchandak]

Refer :https://community.powerbi.com/t5/Desktop/Splitting-multiple-comma-delimited-cells-into-multiple-rows...

https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin

[smpa01]

@LY18  Doable

let
    Source = Web.BrowserContents("https://community.powerbi.com/t5/Desktop/Duplicate-line-based-on-a-list-contain-in-one-column/m-p/927113#M444323"),
    #"Extracted Table From Html" = Html.Table(Source, {{"Column1", "DIV[id='bodyDisplay'] > DIV.lia-message-body-content:nth-child(1) > TABLE:nth-child(6) > TR > :nth-child(1), DIV[id='bodyDisplay'] > DIV.lia-message-body-content:nth-child(1) > TABLE:nth-child(6) > * > TR > :nth-child(1)"}, {"Column2", "DIV[id='bodyDisplay'] > DIV.lia-message-body-content:nth-child(1) > TABLE:nth-child(6) > TR > :nth-child(2), DIV[id='bodyDisplay'] > DIV.lia-message-body-content:nth-child(1) > TABLE:nth-child(6) > * > TR > :nth-child(2)"}, {"Column3", "DIV[id='bodyDisplay'] > DIV.lia-message-body-content:nth-child(1) > TABLE:nth-child(6) > TR > :nth-child(3), DIV[id='bodyDisplay'] > DIV.lia-message-body-content:nth-child(1) > TABLE:nth-child(6) > * > TR > :nth-child(3)"}, {"Column4", "DIV[id='bodyDisplay'] > DIV.lia-message-body-content:nth-child(1) > TABLE:nth-child(6) > TR > :nth-child(4), DIV[id='bodyDisplay'] > DIV.lia-message-body-content:nth-child(1) > TABLE:nth-child(6) > * > TR > :nth-child(4)"}, {"Column5", "DIV[id='bodyDisplay'] > DIV.lia-message-body-content:nth-child(1) > TABLE:nth-child(6) > TR > :nth-child(5), DIV[id='bodyDisplay'] > DIV.lia-message-body-content:nth-child(1) > TABLE:nth-child(6) > * > TR > :nth-child(5)"}, {"Column6", "DIV[id='bodyDisplay'] > DIV.lia-message-body-content:nth-child(1) > TABLE:nth-child(6) > TR > :nth-child(6), DIV[id='bodyDisplay'] > DIV.lia-message-body-content:nth-child(1) > TABLE:nth-child(6) > * > TR > :nth-child(6)"}}, [RowSelector="DIV[id='bodyDisplay'] > DIV.lia-message-body-content:nth-child(1) > TABLE:nth-child(6) > TR, DIV[id='bodyDisplay'] > DIV.lia-message-body-content:nth-child(1) > TABLE:nth-child(6) > * > TR"]),
    #"Promoted Headers" = Table.PromoteHeaders(#"Extracted Table From Html", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID MEASURE", Int64.Type}, {"ID_PARAMETER", Int64.Type}, {"PARAMETER_NAME", type text}, {"EQT_CONCERNED", type text}, {"Group_Value", type number}, {"Group_SPEC", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Index",{"Index", "EQT_CONCERNED"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Other Columns", "EQT_CONCERNED", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"EQT_CONCERNED.1", "EQT_CONCERNED.2", "EQT_CONCERNED.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"EQT_CONCERNED.1", type text}, {"EQT_CONCERNED.2", type text}, {"EQT_CONCERNED.3", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each {[EQT_CONCERNED.1],[EQT_CONCERNED.2],[EQT_CONCERNED.3]}),
    #"Merged Queries" = Table.NestedJoin(#"Added Index", {"Index"}, #"Added Custom", {"Index"}, "Added Custom", JoinKind.LeftOuter),
    #"Expanded Added Custom" = Table.ExpandTableColumn(#"Merged Queries", "Added Custom", {"Custom"}, {"Custom"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Added Custom",{"EQT_CONCERNED"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns", "Custom"),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each ([Custom] <> null))
in
    #"Filtered Rows"