Forum Discussion
MT_1909
2 years agoNew Member
Transformation help please?
Hi Everyone, I have a worksheet with 50k+ rows and would like to create a lineage using power query but I can't seem to get it right. Can someone please help? Thank you so much in advance! ...
wdx223_Daniel
2 years agoCommunity Champion
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZDBCoAgDIbfxXOXnCheqzfIm0gUdLM69f5li20dDATHvv8bYzGqcRq6VjX3H+Ylr1j2Rz63vdThxYFxIJya+B9AXzPWAmvyqwH0DWMjsCG/GkDfMrYC28e/G59XeuUmwDcBcRN4ZwLPBDETaKdqAH3H2AnsyK8G0PeMvcBepXQB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Source DB" = _t, #"Source Table" = _t, #"Source Column" = _t, #"Target DB" = _t, #"Target Table" = _t, #"Target Column" = _t]),
Custom1 = Table.Combine(
Table.Group(
Source,
"Source DB",
{
"n",
each let
a=List.Distinct(
List.Split(
List.Combine(
Table.ToRows(
Table.Skip(
_,
each [Source DB]="" or [Source DB]=null
)
)
),
3)
)
in
#table(
Table.ColumnNames(_)&
List.TransformMany(
{1..List.Count(a)-2},
each List.LastN(Table.ColumnNames(Source),3),
(x,y)=>y&" Hop "&Text.From(x)
),
{List.Combine(a)}
)
},
0,
(x,y)=>Byte.From(y=null or y="")
)
[n])
in
Custom1- MT_19092 years agoNew Member
Hi wdx223_Daniel,
Thank you so much for the code, I've tested it and it worked, perfectly what I'm looking for.
I would like to test the code using my xls file but am having trouble in how to ...
replace this ...
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZDBCoAgDIbfxXOXnCheqzfIm0gUdLM69f5li20dDATHvv8bYzGqcRq6VjX3H+Ylr1j2Rz63vdThxYFxIJya+B9AXzPWAmvyqwH0DWMjsCG/GkDfMrYC28e/G59XeuUmwDcBcRN4ZwLPBDETaKdqAH3H2AnsyK8G0PeMvcBepXQB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Source DB" = _t, #"Source Table" = _t, #"Source Column" = _t, #"Target DB" = _t, #"Target Table" = _t, #"Target Column" = _t]),to point to an xls file ...
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content]
in
SourceThank you so much again and am looking forward to your response 🙂
- wdx223_Daniel2 years agoCommunity Champion
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], Custom1 = Table.Combine( Table.Group( Source, "Source DB", { "n", each let a=List.Distinct( List.Split( List.Combine( Table.ToRows( Table.Skip( _, each [Source DB]="" or [Source DB]=null ) ) ), 3) ) in #table( Table.ColumnNames(_)& List.TransformMany( {1..List.Count(a)-2}, each List.LastN(Table.ColumnNames(Source),3), (x,y)=>y&" Hop "&Text.From(x) ), {List.Combine(a)} ) }, 0, (x,y)=>Byte.From(y=null or y="") ) [n]) in Custom1