Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
Hi,
I have a table like this:
How could i transform it into this format, in PowerQuery
Thank you.
Solved! Go to 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:
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.
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:
Thanks Ovettea, great solution!
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):
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
81 | |
75 | |
60 | |
37 | |
33 |
User | Count |
---|---|
102 | |
56 | |
52 | |
46 | |
40 |