Parse a table from Jira in Power Query

Hi dandelion  To acheive your desired table, first split the column by delimeter "|". Then remove all the unnecessary column which is blank. After that,  merge all the pool seperated by "|" and merget all the number seperated by "|" (Keep orginal serial). Then tranpose, split and transpose again. Use first row as header and you are done. See images:

You have format like that:

 

After spliting by "|", You will get blank columns:

After removing blank column:

After merged all the pools and numbers, seperated by "|", you will get only 2 columns:

After Transpose, you will get:

 

After split by "|", and you will get:

Now transpose back, and you will get:

After promoting first row as header, you will get your desired result:

 

Check the steps, in the attached excel file.

 

Hope this helps!!

If this solved your problem, please accept it as a solution and a kudos!!

 

Best Regards,
Shahariar Hafiz

 

Hi dandelion, another solution:

Output

let
    Source = #table(null, {{"|Pool||Number|| |Fruits|5| |Veggies|30| |Dairy|-| |grain|2| |eggs|-|"}}),
    Transformed = Table.Combine(Table.TransformColumns(Source,{{"Column1", each Table.PromoteHeaders(Table.FromRows(List.Split(List.ReplaceMatchingItems(List.Select(Text.Split(_, "|"), (x)=> Text.Trim(x) <> ""), {{"-", null}}), 2))), type text}})[Column1])
in
    Transformed