Forum Discussion
Transform data which has multiple columns with the same name but different values.
Below is the data, which I have multiple cash and credit columns. Date Salesman Name Branch Item E1 Item E2 Item E3 Item E4 CASH CREDIT Item S1 Item S2 Item S3 CASH CREDIT 01...
Hi Anonymous
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZU8xDoMwDPwKyoxEnARaRlqQytKh6VAJMaQoEkNhaPm/6qMhUsVw9tk5n52uE7VbvEiFdS//mdycXN2E+vR28zAyaRc/JQ1FpiLTkRlm58pekG5N3d63FxvnbJyzeqfu005IykhmSiqYOcaDAalkYLhARqER8tDVkBv9a+98nvgZgyDFLSaAVjeww3Fl+s9L0d5rYFRhM+XhsBJFuW0oQ7fIt1vJiL7/Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Colonne 1" = _t, #"Colonne 2" = _t, #"Colonne 3" = _t, #"Colonne 4" = _t, #"Colonne 5" = _t, #"Colonne 6" = _t, #"Colonne 7" = _t, #"Colonne 8" = _t, #"Colonne 9" = _t, #"Colonne 10" = _t, #"Colonne 11" = _t, #"Colonne 12" = _t, #"Colonne 13" = _t, #"Colonne 14" = _t]),
#"CASH position" = List.PositionOf(Record.ToList(Source{0}),"CASH",Occurrence.All),
#"Branch and CREDIT position" = List.PositionOfAny(Record.ToList(Source{0}), {"Branch","CREDIT"}, Occurrence.All),
#"Item position" = List.Transform(
List.Zip({
List.Transform(List.RemoveLastN(#"Branch and CREDIT position",1), each _+1),
List.Transform(#"CASH position", each _-1)}),
each {_{0}.._{1}}),
ToRows = Table.ToRows(Source),
#"First Row" = ToRows{0},
Data = List.Skip(ToRows),
TransformMany = List.TransformMany(
Data,
each #"Item position",
(x,y) => List.FirstN(x,3) &
{Table.FromRows(List.Transform(y, each {#"First Row"{_}, x{_}}), {"Items", "Quantity"})} &
{x{List.Max(y)+1}} & {x{List.Max(y)+2}} ),
FromRows = Table.FromRows(TransformMany, List.FirstN(#"First Row", 3) & {"Data"} & List.LastN(#"First Row", 2)),
Expand = Table.ExpandTableColumn(FromRows, "Data", {"Items", "Quantity"}, {"Items", "Quantity"})
in
ExpandStephane
Hi Anonymous
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZU8xDoMwDPwKyoxEnARaRlqQytKh6VAJMaQoEkNhaPm/6qMhUsVw9tk5n52uE7VbvEiFdS//mdycXN2E+vR28zAyaRc/JQ1FpiLTkRlm58pekG5N3d63FxvnbJyzeqfu005IykhmSiqYOcaDAalkYLhARqER8tDVkBv9a+98nvgZgyDFLSaAVjeww3Fl+s9L0d5rYFRhM+XhsBJFuW0oQ7fIt1vJiL7/Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Colonne 1" = _t, #"Colonne 2" = _t, #"Colonne 3" = _t, #"Colonne 4" = _t, #"Colonne 5" = _t, #"Colonne 6" = _t, #"Colonne 7" = _t, #"Colonne 8" = _t, #"Colonne 9" = _t, #"Colonne 10" = _t, #"Colonne 11" = _t, #"Colonne 12" = _t, #"Colonne 13" = _t, #"Colonne 14" = _t]),
#"CASH position" = List.PositionOf(Record.ToList(Source{0}),"CASH",Occurrence.All),
#"Branch and CREDIT position" = List.PositionOfAny(Record.ToList(Source{0}), {"Branch","CREDIT"}, Occurrence.All),
#"Item position" = List.Transform(
List.Zip({
List.Transform(List.RemoveLastN(#"Branch and CREDIT position",1), each _+1),
List.Transform(#"CASH position", each _-1)}),
each {_{0}.._{1}}),
ToRows = Table.ToRows(Source),
#"First Row" = ToRows{0},
Data = List.Skip(ToRows),
TransformMany = List.TransformMany(
Data,
each #"Item position",
(x,y) => List.FirstN(x,3) &
{Table.FromRows(List.Transform(y, each {#"First Row"{_}, x{_}}), {"Items", "Quantity"})} &
{x{List.Max(y)+1}} & {x{List.Max(y)+2}} ),
FromRows = Table.FromRows(TransformMany, List.FirstN(#"First Row", 3) & {"Data"} & List.LastN(#"First Row", 2)),
Expand = Table.ExpandTableColumn(FromRows, "Data", {"Items", "Quantity"}, {"Items", "Quantity"})
in
Expand
Stephane
I tried to implement this into my data but I'm facing an error.
Expression.Error: We cannot apply operator .. to types Null and Number.
I have attached the file.
https://docs.google.com/spreadsheets/d/1hFQ8lTrGRyIV-vsDgOwJyolKP1lTLzoC/edit?usp=sharing&ouid=104076749508283104516&rtpof=true&sd=true