Forum Discussion
transformation rows into columns
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
- spinfuzer2 years agoSolution Sage
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.