Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello everyone,
I am working on an Excel file with rows that have sub-rows, I would like to do a transformation by bringing these sub-rows into columns.
Each month we will have a file of this format in the sharppoint.https://www.dropbox.com/scl/fi/rwe9m0fm236uyly9njp64/data.xlsx?rlkey=95pkam4g7qw72jd1q8asanepy&dl=0
help me pleaz
Copy and paste the entire code for example.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZNNbsIwEIWvYmU9Sj2Of+IlCFA3lIqqK8QigqQgpYoELRJH4hxcrAHPJFHUHWGR5Mlv/I3nWVmtIi8+zmWZiZcIounnsn4LehCMtjFiZ6m3jFLGUkZrWEXjt5l4jDCrDkW2/3kM4sV4V53zzjTobx5EVskgEMGlrMk0bHpIGw+8ZFk3Su78+WL6JHJ//GHpvs5IjLNdmwvjE6o1YB3DaIUxGhqJgFxVt7YBPpq8PwO7vF6+9vkzyPc0XrPtuY3DhppUGUZ4Q/vIci1dsXRcgyZGE8KYL0bDU3tZDMb1uk5inn1n2+q389MQ1nofm5tIQHsXJFmGWQoarNNUozBO3J1/zMtCHPPDab9pTy9DldO0L+EbIqNlO+rm6VvPa9N/f5chmJP81PB02Ga5KgFUNJ3uE0Ez0xpFCcg4tZ2bE0U47/WSD9xi/Qc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Prenom / Projet / facturation" = _t, Devise = _t, rec_quantité = _t, rec_montant = _t, rec_total = _t, dep_quantité = _t, dep_montant = _t, dep_total = _t, marge = _t, taux = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Prenom / Projet / facturation", type text}, {"Devise", type text}, {"rec_quantité", type number}, {"rec_montant", type number}, {"rec_total", type number}, {"dep_quantité", type number}, {"dep_montant", Int64.Type}, {"dep_total", Int64.Type}, {"marge", type text}, {"taux", type number}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if not Text.EndsWith([#"Prenom / Projet / facturation"],"/") then null else Text.BeforeDelimiter([#"Prenom / Projet / facturation"], "/")),
#"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Down", each not Text.Contains([#"Prenom / Projet / facturation"], "/")),
#"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",{"Custom", "Prenom / Projet / facturation", "Devise", "rec_quantité", "rec_montant", "rec_total", "dep_quantité", "dep_montant", "dep_total", "marge", "taux"}),
#"Added Index" = Table.AddIndexColumn(#"Reordered Columns", "Index", 0, 1, Int64.Type),
#"Calculated Modulo" = Table.TransformColumns(#"Added Index", {{"Index", each Number.Mod(_, 2), type number}}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Calculated Modulo", {{"Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Calculated Modulo", {{"Index", type text}}, "en-US")[Index]), "Index", "Prenom / Projet / facturation"),
#"Reordered Columns1" = Table.ReorderColumns(#"Pivoted Column",{"Custom", "0", "1", "Devise", "rec_quantité", "rec_montant", "rec_total", "dep_quantité", "dep_montant", "dep_total", "marge", "taux"})
in
#"Reordered Columns1"
Hello @spinfuzer,
Thanks you.
But, the sub rows contains "/" is excluded.
Is it possible not to exclude sublines that contain "/"
thanks
https://www.dropbox.com/scl/fi/rwe9m0fm236uyly9njp64/data.xlsx?rlkey=95pkam4g7qw72jd1q8asanepy&dl=0
Hello @alsolok ,
DId you follow the entires set of steps? We purposely did not include the other rows because we are trying to do a fill down on the rows with "/".
1) Copy only the first rows to another column (in this case we use ends with "/" otherwise null)
2) Fill down those rows
3) Filter out the first rows
4) Create index starting from 0.
5) Transform index modulo 2.
6) Pivot on Index and do not aggregate on the "Prenom / Projet / facturation" column.
7) rename and reorder your columns as desired.
What would you like your final result to look like from your posted data?
I want this result.
This is the link for the data
https://www.dropbox.com/scl/fi/rwe9m0fm236uyly9njp64/data.xlsx?rlkey=95pkam4g7qw72jd1q8asanepy&dl=0
Hi @alsolok
You can put the following code to advanced editor in power query
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZNNbsIwEIWvYmU9Sj2Of+IlCFA3lIqqK8QigqQgpYoELRJH4hxcrAHPJFHUHWGR5Mlv/I3nWVmtIi8+zmWZiZcIounnsn4LehCMtjFiZ6m3jFLGUkZrWEXjt5l4jDCrDkW2/3kM4sV4V53zzjTobx5EVskgEMGlrMk0bHpIGw+8ZFk3Su78+WL6JHJ//GHpvs5IjLNdmwvjE6o1YB3DaIUxGhqJgFxVt7YBPpq8PwO7vF6+9vkzyPc0XrPtuY3DhppUGUZ4Q/vIci1dsXRcgyZGE8KYL0bDU3tZDMb1uk5inn1n2+q389MQ1nofm5tIQHsXJFmGWQoarNNUozBO3J1/zMtCHPPDab9pTy9DldO0L+EbIqNlO+rm6VvPa9N/f5chmJP81PB02Ga5KgFUNJ3uE0Ez0xpFCcg4tZ2bE0U47/WSD9xi/Qc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Prenom / Projet / facturation" = _t, Devise = _t, rec_quantité = _t, rec_montant = _t, rec_total = _t, dep_quantité = _t, dep_montant = _t, dep_total = _t, marge = _t, taux = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Prenom / Projet / facturation", type text}, {"Devise", type text}, {"rec_quantité", type number}, {"rec_montant", type number}, {"rec_total", type number}, {"dep_quantité", type number}, {"dep_montant", Int64.Type}, {"dep_total", Int64.Type}, {"marge", type number}, {"taux", type number}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if Text.Contains([#"Prenom / Projet / facturation"],"/") then Table.RowCount(Table.SelectRows(#"Added Index",(x)=>x[Index]<=[Index] and Text.Contains(x[#"Prenom / Projet / facturation"],"/"))) else null),
#"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
#"Added Custom1" = Table.AddColumn(#"Filled Down", "Custom.1", each let
a=List.Max(Table.SelectRows(#"Filled Down",(x)=>x[Index]<=[Index] and Text.Contains(x[#"Prenom / Projet / facturation"],"/"))[Index])
in if [Index]>=a and [Index] <a+3 then [Custom] else [Custom]+0.5),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each let a=Table.SelectRows(#"Added Custom1",(x)=>x[Custom.1]=[Custom.1])[#"Prenom / Projet / facturation"],
b= if [Custom.1]-Number.RoundDown([Custom.1])=0.5 then List.Max(Table.SelectRows(#"Added Custom1",(x)=>x[Index]<[Index] and Text.Contains(x[#"Prenom / Projet / facturation"],"/"))[Index]) else null,
c=List.Max(Table.SelectRows(#"Added Custom1",(x)=>x[Index]=b)[#"Prenom / Projet / facturation"])
in Text.Combine({c,Text.Combine(a,"/")},"/")),
#"Replaced Value" = Table.ReplaceValue(#"Added Custom2","//","/",Replacer.ReplaceText,{"Custom.2"}),
#"Added Custom3" = Table.AddColumn(#"Replaced Value", "Custom.3", each Number.Mod([Index],3)),
#"Filtered Rows" = Table.SelectRows(#"Added Custom3", each ([Custom.3] <> 1)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Prenom / Projet / facturation", "Index", "Custom", "Custom.1", "Custom.3"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Custom.2", "Devise", "rec_quantité", "rec_montant", "rec_total", "dep_quantité", "dep_montant", "dep_total", "marge", "taux"}),
#"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Custom.2", "Prenom / Projet / facturation"}}),
#"Removed Duplicates" = Table.Distinct(#"Renamed Columns", {"Prenom / Projet / facturation"})
in
#"Removed Duplicates"
Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.