Complex Text Transformation in PowerQuery

[jichaowu]

Hi,

I have a table like this:

How could i transform it into this format, in PowerQuery

Thank you.

[Ashish_Mathur]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Text", type text}, {"Number", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","#","",Replacer.ReplaceText,{"Number"}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Replaced Value", {{"Number", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Number"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Number", type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type1",{{"Number", Text.Trim, type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Trimmed Text", "Number", Splitter.SplitTextByDelimiter("to", QuoteStyle.Csv), {"Number.1", "Number.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Number.1", Int64.Type}, {"Number.2", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type2", "Custom", each {Number.From([Number.1])..Number.From([Number.2])}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Number.1", "Number.2"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Columns", "Custom.1", each "#"&Number.ToText([Custom])),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Custom"})
in
    #"Removed Columns1"

Hope this helps.

[ovetteabejuela · Accepted solution]

Sorry I think the first solution I just gave away is missing something... you want to consider the range correct? Try this instead:

Same everything else except that you have to use this code:

let
    Source = Excel.CurrentWorkbook(){[Name="Data_"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Source", type text}, {"Range", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Range", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Range.1", "Range.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Range.1", type text}, {"Range.2", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Source"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Trimmed Text" = Table.TransformColumns(#"Removed Columns",{{"Value", Text.Trim, type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Trimmed Text", "Value", Splitter.SplitTextByDelimiter("to", QuoteStyle.Csv), {"Value.1", "Value.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Value.1", type text}, {"Value.2", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type2","#","",Replacer.ReplaceText,{"Value.1"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Replaced Value",{{"Value.1", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type3", "Range", each {[Value.1]..[Value.2]}),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Value.1", "Value.2"}),
    #"Expanded Range" = Table.ExpandListColumn(#"Removed Columns1", "Range"),
    #"Changed Type4" = Table.TransformColumnTypes(#"Expanded Range",{{"Range", type text}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type4", "Final Data", each "#" & Text.PadStart([Range],2,"0"))
in
    #"Added Custom1"

Here's the poutput for this one:

[jichaowu]

Thanks Ovettea, great solution!

[ovetteabejuela]

I created this in excel but it's basically the same...

First I created a table (named "Data_") 

Create a blank query and paste the code below.

let
    Source = Excel.CurrentWorkbook(){[Name="Data_"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Source", type text}, {"Range", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",","," to ",Replacer.ReplaceText,{"Range"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value", "Range", Splitter.SplitTextByDelimiter("to", QuoteStyle.Csv), {"Range.1", "Range.2", "Range.3", "Range.4"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Range.1", type text}, {"Range.2", Int64.Type}, {"Range.3", type text}, {"Range.4", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Source"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns",{{"Value", type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type2",{{"Value", Text.Trim, type text}}),
    #"Replaced Value1" = Table.ReplaceValue(#"Trimmed Text","#","",Replacer.ReplaceText,{"Value"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value1", "Final Data", each "#" & Text.PadStart([Value],2,"0"))
in
    #"Added Custom"

Edit (here's the output):