Forum Discussion

Samhunt's avatar
Samhunt
Helper II
2 years ago

Need help with power query

Hi Guys I am new to power bi and I need some help.    I will not post any tables as it gets my message as spam.    This is the formula that I use in excel and I add it in Column A and I start fro...
Need Help
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Samhunt 

You can put the following code to advanced editor in power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("vZCxTsMwEEB/xcrUSq1yd47deCyIDTFAt6iDCaZYbezKOAhWvoev4kuI00ikUrNW8uJ3T7buVVVGwNabR7bcBOts/GIznLP1UYfYGBezRTac7aLKbrzfW7djwbyaYFxtusHtm6n3S99G9qJjAnefMeh3phvfushmv98/86T5pjGhtvrAGh121g2T9CwqUSoC0WmwyhFyAuLdRUIivUKAEoVICtJlBcuCAHqFxkpPBkUVXBKWSSmnPuKEICgBkSNeUJ7a5+ijPpzCkPp//0EfNbu3H6kRwVXadStLhVymMDBaicqzdhx4oqimFBIkUSVaTColkVwlgKMwI+U8DCKdJts/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Column2]="" and [Column3]="" and [Column4]="" then [Column1] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Filled Down",{"Custom", "Column1", "Column2", "Column3", "Column4"})
in
    #"Reordered Columns"

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.

Samhunt's avatar
Samhunt
Helper II
2 years ago

Hello Anonymous

 

This works, but it will only return me the information that you posted, if the data set is bigger, it is a few thousand raws, it will still return me the first 15 raws. 

 

This code was posted in the Advanced Editor, let
Source = GoogleSheets.Contents("https://docs.google.com/spreadsheets/d/15H2YmBRAGo0fF-CEZkGNBD35KnRlwJscaHsZ0h_faEo/edit?usp=drive_link"),
Sheet1_Table = Source{[name="Sheet1",ItemKind="Table"]}[Data],
#"Removed Columns" = Table.RemoveColumns(Sheet1_Table,{"Column2", "Column3", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column5"})
in
#"Removed Columns" 

 

I had to remove it and paste the code you shared with me. 

 

What I totally don't understand is the following, I removed my data set and went in the initial source and changed it. I removed the top 15 rows which are the data that are used by the results I received. 

 

Then I imported it again, so as you understand the data was different. 

 

I copied the code you provided in advanced editor and the result was the same. Different data same result very strange.