Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Here is my problem: my database has lines like below. Each single record contains into Steps column a CVS list representing sequential steps each record needs to go through.
What I need is to transform above table into below one with each row having start and end for each single step.
I've tried splitting the column and than unpivoting but failed. Started to write m language but cannot move on since I'm a beginner.
Thanks in advance
Solved! Go to Solution.
@acitsme Please try below steps:
In "Power Query Editor" use the "Split Column" option and in advance options select rows as below
Rename the "Steps" field to Start and then add an Index field available under "Add Column" tab
Here is the M-code generated for the above steps (For reference)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKjNU0lEqMwISiTpJOsk6KUqxOkBRY5CoCZBI18nQydTJ0clVio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Steps = _t]), #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Steps", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Steps"), #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1", type text}, {"Column2", type text}, {"Steps", type text}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Steps", "Start"}}), #"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 1, 1) in #"Added Index"
Now back to the report view, add a new column using DAX as below
End = VAR _NextC1 = LOOKUPVALUE(Test229ColumnSplit[Column1],Test229ColumnSplit[Index],Test229ColumnSplit[Index]+1) VAR _NextC2 = LOOKUPVALUE(Test229ColumnSplit[Column2],Test229ColumnSplit[Index],Test229ColumnSplit[Index]+1) VAR _NextVal = LOOKUPVALUE(Test229ColumnSplit[Start],Test229ColumnSplit[Index],Test229ColumnSplit[Index]+1) RETURN IF(_NextC1=Test229ColumnSplit[Column1] && _NextC2=Test229ColumnSplit[Column2],_NextVal,BLANK())
The output looks like below but you can filter out the blank values as shown in below second screenshot
Proud to be a PBI Community Champion
@acitsme Please try below steps:
In "Power Query Editor" use the "Split Column" option and in advance options select rows as below
Rename the "Steps" field to Start and then add an Index field available under "Add Column" tab
Here is the M-code generated for the above steps (For reference)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKjNU0lEqMwISiTpJOsk6KUqxOkBRY5CoCZBI18nQydTJ0clVio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Steps = _t]), #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Steps", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Steps"), #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1", type text}, {"Column2", type text}, {"Steps", type text}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Steps", "Start"}}), #"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 1, 1) in #"Added Index"
Now back to the report view, add a new column using DAX as below
End = VAR _NextC1 = LOOKUPVALUE(Test229ColumnSplit[Column1],Test229ColumnSplit[Index],Test229ColumnSplit[Index]+1) VAR _NextC2 = LOOKUPVALUE(Test229ColumnSplit[Column2],Test229ColumnSplit[Index],Test229ColumnSplit[Index]+1) VAR _NextVal = LOOKUPVALUE(Test229ColumnSplit[Start],Test229ColumnSplit[Index],Test229ColumnSplit[Index]+1) RETURN IF(_NextC1=Test229ColumnSplit[Column1] && _NextC2=Test229ColumnSplit[Column2],_NextVal,BLANK())
The output looks like below but you can filter out the blank values as shown in below second screenshot
Proud to be a PBI Community Champion
WOW! Super fast and super cool!
The combination of M code and column with DAX is so smart!
Thanks
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
93 | |
86 | |
80 | |
69 | |
68 |
User | Count |
---|---|
226 | |
129 | |
119 | |
83 | |
77 |