Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
Anonymous
Not applicable

Complex Text Transformation in PowerQuery

Hi,

 

I have a table like this:

Capture.PNG

 

How could i transform it into this format, in PowerQuery

Capture1.PNG

 

Thank you.

1 ACCEPTED SOLUTION
ovetteabejuela
Impactful Individual
Impactful Individual

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:

Output_Av2.PNG

View solution in original post

4 REPLIES 4
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
ovetteabejuela
Impactful Individual
Impactful Individual

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:

Output_Av2.PNG

Anonymous
Not applicable

Thanks Ovettea, great solution!

ovetteabejuela
Impactful Individual
Impactful Individual

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

 

First I created a table (named "Data_") 

My Answer_A.PNG

 

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):

 

Output_A.PNG 

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.