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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
jichaowu
Frequent Visitor

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

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors